﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Transactions;
using Simple.Services.System;
using OfficeOpenXml;
using System.IO;
using Simple.Services.System.QHSEViolationCategory.Models;
using Simple.Services.System.QHSEDanger.Models.AnalysisInput;
using System.Globalization;
using System.Security.Cryptography;
using Simple.Services;
using System.Reflection;
using OfficeOpenXml.FormulaParsing.LexicalAnalysis;
using Newtonsoft.Json;
using System.Text.Json.Serialization;
using Microsoft.EntityFrameworkCore.Infrastructure;
using System.Data;
using Oracle.ManagedDataAccess.Client;
using static Microsoft.EntityFrameworkCore.DbLoggerCategory;
using NLog.Web.LayoutRenderers;
using Microsoft.AspNetCore.Mvc;
using System.Drawing.Printing;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.EntityFrameworkCore;
using Simple.Services.System.QHSEXyViolation.Models;
using Simple.Repository.Extensions;

public class QHSEDangerService
{
    private readonly IDbConfig _dbConfig;
    private readonly SimpleDbContext _context;
    private readonly CustomDbContext _customDbContext;
    private readonly CacheService _cacheService;
    private readonly QHSEOrganizationService _organizationService;
    private readonly QHSEDangerCategoryService _categoryService;
    private readonly QHSEKeyWorkSpecialtyService _keyWorkSpecialtyService;
    private readonly QHSEDictionaryService _dictionaryService;
    private readonly QHSEViolationHSEService _violationHSEService;
    private readonly QHSEKeyWorkTypeService _keyWorkTypeService;
    private readonly QHSELocationService _locaitionService;
    private readonly IQhseFileService _qhseFileService;
    private readonly IServiceScopeFactory _serviceScopeFactory;

    public QHSEDangerService(IDbConfig dbConfig, SimpleDbContext context, CacheService cacheService, QHSEOrganizationService organizationService, QHSEDangerCategoryService categoryService, QHSEDictionaryService dictionaryService, QHSEViolationHSEService violationHSEService, QHSEKeyWorkSpecialtyService keyWorkSpecialtyService, QHSEKeyWorkTypeService keyWorkTypeService, QHSELocationService locaitionService,IQhseFileService qhseFileService, CustomDbContext customDbContext, IServiceScopeFactory serviceScopeFactory)
    {
        _dbConfig = dbConfig;
        _context = context;
        _cacheService = cacheService;
        _organizationService = organizationService;
        _categoryService = categoryService;
        _dictionaryService = dictionaryService;
        _violationHSEService = violationHSEService;
        _keyWorkSpecialtyService = keyWorkSpecialtyService;
        _keyWorkTypeService = keyWorkTypeService;
        _locaitionService = locaitionService;
        _qhseFileService = qhseFileService;
        _customDbContext = customDbContext;
        _serviceScopeFactory = serviceScopeFactory;
    }
    public async Task<List<QHSEDangerModel>> GetBySqlAsync(IdInputModel id)
    {
        var violations = await _context.Set<QHSESysDanger>().FromSqlRaw(id.Id).ToListAsync();
        var models = MapperHelper.Map<List<QHSEDangerModel>>(violations);
        return models;
    }
    public async Task<List<QHSEDangerModel>> GetAsync()
    {
        var dangers = await _context.Set<QHSESysDanger>().ToListAsync();
        var models = MapperHelper.Map<List<QHSEDangerModel>>(dangers);
        foreach (var model in models)
        {
            if (model.DangerPic != null)
            {
                var files = await utilService.GetFilesAsync(Directory.GetCurrentDirectory() + @"/DANGER/" + model.DangerPic,_qhseFileService);
                model.DangerFiles = files;
            }
            if (model.ReformerPic != null)
            {
                var files = await utilService.GetFilesAsync(Directory.GetCurrentDirectory() + @"/REFORMER/" + model.ReformerPic,_qhseFileService);
                model.ReformerFiles = files;
            }
        }
        return models;
    }

    public async Task<List<QHSEDangerModel>> GetBySqlAsync()
    {
        string sql = "SELECT * FROM QHSE_XY_DANGER";

        var dangers = await _customDbContext.GetAllFieldsAsync<QHSESysDanger>(
            sql);
        var models = MapperHelper.Map<List<QHSEDangerModel>>(dangers);
        foreach (var model in models)
        {
            if (model.DangerPic != null)
            {
                var files = await utilService.GetFilesAsync(Directory.GetCurrentDirectory() + @"/DANGER/" + model.DangerPic, _qhseFileService);
                model.DangerFiles = files;
            }
            if (model.ReformerPic != null)
            {
                var files = await utilService.GetFilesAsync(Directory.GetCurrentDirectory() + @"/REFORMER/" + model.ReformerPic, _qhseFileService);
                model.ReformerFiles = files;
            }
        }
        return models;
    }
    public async Task<List<QHSEDangerModel>> GetBySqlExAsync()
    {
        string sql = "SELECT * FROM QHSE_XY_DANGER WHERE QHSE_XY_DANGER_ID = '1338'";

        var dangers = await _customDbContext.GetPartialAsync<QHSESysDanger>(
            sql,"QHSE_XY_DANGER_ID", "CHECK_TYPE");
        var models = MapperHelper.Map<List<QHSEDangerModel>>(dangers);
        foreach (var model in models)
        {
            if (model.DangerPic != null)
            {
                var files = await utilService.GetFilesAsync(Directory.GetCurrentDirectory() + @"/DANGER/" + model.DangerPic, _qhseFileService);
                model.DangerFiles = files;
            }
            if (model.ReformerPic != null)
            {
                var files = await utilService.GetFilesAsync(Directory.GetCurrentDirectory() + @"/REFORMER/" + model.ReformerPic, _qhseFileService);
                model.ReformerFiles = files;
            }
        }
        return models;
    }
    private static string EscapeLikePattern(string pattern)
    {
        return pattern.Replace("'", "''").Replace("/", "//").Replace("_", "/_").Replace("%", "/%");
    }
    public async Task<PageResultModel<QHSEDangerModel>> GetPageAsync(DangerPageInputModel input)
    {

        var result = new PageResultModel<QHSEDangerModel>();
        if (!string.IsNullOrEmpty(input.CheckOrganizationName) || !string.IsNullOrEmpty(input.DangerOrganizationName)
            || !string.IsNullOrEmpty(input.Status) || !string.IsNullOrEmpty(input.DangerType)
            || !string.IsNullOrEmpty(input.UserName) || input.TimeOn != null || input.TimeEnd != null)
        {
            var query = _context.Set<QHSESysDanger>()
                .AsNoTracking()
                        .Where(u =>
                            u.CheckDate >= input.TimeOn &&
                            u.CheckDate <= input.TimeEnd &&
                            u.CheckType == input.CheckType)
                        .AsQueryable();

            // 根据条件查询
            if (!string.IsNullOrEmpty(input.DangerOrganizationName))
            {
                var org = await _context.Set<QHSESysOrganization>().FindAsync(input.DangerOrganizationId);
                if (org != null)
                {
                    var escapedNamePath = EscapeLikePattern(org.NamePath);
                    query = query
                    .Join(_context.Set<QHSESysOrganization>(), // Join with Organization table
                        danger => danger.OrganizationId,
                        organization => organization.OrganizationId,
                        (danger, organization) => new { Danger = danger, Organization = organization })
                    .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                    .Select(joinResult => joinResult.Danger);
                }
            }
            if (!string.IsNullOrEmpty(input.UserName))
            {
                query = query.Where(u => EF.Functions.Like(u.CheckName, $"%{input.UserName}%"));
            }
            if (!string.IsNullOrEmpty(input.DangerType) || !string.IsNullOrEmpty(input.DangerTypeCode))
            {
                query = query.Where(u =>u.DangerType.Equals(input.DangerType));
            }
            if (!string.IsNullOrEmpty(input.Status))
            {
                query = query.Where(u => u.Status.Equals(input.Status));
            }
            if (!string.IsNullOrEmpty(input.CheckOrganizationName))
            {
                var corg = await _context.Set<QHSESysOrganization>().FindAsync(input.CheckOrganizationId);
                if (corg != null)
                {
                    var escapedcNamePath = EscapeLikePattern(corg.NamePath);
                    query = query
                        .Join(_context.Set<QHSESysOrganization>(), // Join with QHSEOrganization table
                            joinResult => joinResult.CheckOrganizationId,
                            organization => organization.OrganizationId,
                            (joinResult, organization) => new { Danger = joinResult, Organization = organization })
                        .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedcNamePath}%", "/"))
                        .Select(joinResult => joinResult.Danger);
                }
            }
            // 获取总数量
            var totalRows = await query.CountAsync();

            // 分页查询
            var dangers =await query
                .OrderByDescending(u => u.CheckDate)
                .Skip((input.PageNo - 1) * input.PageSize)
                .Take(input.PageSize)
                .Select(danger => new QHSEDangerModel
                {
                    qhseXyDangerId = danger.QhseXyDangerId,
                    UserId = danger.UserId,
                    OrganizationName = _context.Set<QHSESysOrganization>()
                                                  .Where(org => org.OrganizationId == danger.OrganizationId)
                                                  .Select(org => org.NamePath)
                                                  .FirstOrDefault(),
                    WorkItem = danger.WorkItem,
                    CheckName = danger.CheckName,
                    CheckDate = danger.CheckDate,
                    OrganizationId = danger.OrganizationId, // 获取 OrganizationName
                    Location = danger.Location,
                    DangerType = danger.DangerType,
                    Description = danger.Description,
                    CheckOrganizationId = danger.CheckOrganizationId,
                    Status = danger.Status
                }).ToListAsync();
            result = new PageResultModel<QHSEDangerModel>
            {
                TotalRows = totalRows,
                Rows = dangers,
                PageNo = input.PageNo,
                PageSize = input.PageSize
            };

        }
        result.SetPage(input);
        result.CountTotalPage();

        return result;

    }


    public async Task<PageResultModel<QHSEDangerModel>> GetPageBySqlAsync(DangerPageInputModel input)
    {
        var result = new PageResultModel<QHSEDangerModel>();

        if (!string.IsNullOrEmpty(input.CheckOrganizationName) || !string.IsNullOrEmpty(input.DangerOrganizationName)
            || !string.IsNullOrEmpty(input.Status) || !string.IsNullOrEmpty(input.DangerType)
            || !string.IsNullOrEmpty(input.UserName) || input.TimeOn != null || input.TimeEnd != null)
        {
            var tableName = EntityHelper.GetTableName<QHSESysDanger>();
            var tableNameOrg = EntityHelper.GetTableName<QHSESysOrganization>();
            var propertyColumnMap = EntityHelper.GetPropertyDatabaseColumnMap<QHSESysDanger>();
            var propertyColumnMapOrg = EntityHelper.GetPropertyDatabaseColumnMap<QHSESysOrganization>();

            var sqlBuilder = new StringBuilder();
            sqlBuilder.AppendLine($@"SELECT /*+ INDEX(q idx_qhse_xy_danger_cover) */
                                    q.QHSE_XY_DANGER_ID AS qhseXyDangerId, 
                                    q.USER_ID AS UserId, 
                                    q.WORK_ITEM AS WorkItem, 
                                    q.CHECK_NAME AS CheckName, 
                                    q.CHECK_DATE AS CheckDate, 
                                    q0.NAME_PATH AS OrganizationName, 
                                    q.LOCATION AS Location, 
                                    q.DANGER_TYPE AS DangerType, 
                                    q.DESCRIPTION AS Description, 
                                    q.CHECKORGANIZATIONID AS CheckOrganizationId, 
                                    q.STATUS AS Status");
            sqlBuilder.AppendLine($"FROM {tableName} q");
            sqlBuilder.AppendLine($"INNER JOIN {tableNameOrg} q0 ON q.{propertyColumnMap["OrganizationId"]} = q0.{propertyColumnMapOrg["OrganizationId"]}");
            sqlBuilder.AppendLine("WHERE 1=1");

            var parameters = new List<OracleParameter>();

            if (input.TimeOn.HasValue)
            {
                sqlBuilder.AppendLine($"AND q.{propertyColumnMap["CheckDate"]} >= :TimeOn");
                parameters.Add(new OracleParameter("TimeOn", input.TimeOn.Value));
            }

            if (input.TimeEnd.HasValue)
            {
                sqlBuilder.AppendLine($"AND q.{propertyColumnMap["CheckDate"]} <= :TimeEnd");
                parameters.Add(new OracleParameter("TimeEnd", input.TimeEnd.Value));
            }

            if (!string.IsNullOrEmpty(input.CheckType))
            {
                sqlBuilder.AppendLine($"AND q.{propertyColumnMap["CheckType"]} = :CheckType");
                parameters.Add(new OracleParameter("CheckType", input.CheckType));
            }

            if (!string.IsNullOrEmpty(input.DangerOrganizationName))
            {
                var org = await _context.Set<QHSESysOrganization>().FindAsync(input.DangerOrganizationId);
                if (org != null)
                {
                    var escapedNamePath = EscapeLikePattern(org.NamePath);

                    sqlBuilder.AppendLine($"AND EXISTS (" +
                        $"SELECT 1 " +
                        $"FROM {tableNameOrg} q2 " +
                        $"WHERE q2.{propertyColumnMapOrg["OrganizationId"]} = q.{propertyColumnMap["OrganizationId"]} " +
                        $"AND q2.NAME_PATH LIKE '{escapedNamePath}%' ESCAPE '/')");
                }
            }
            if (!string.IsNullOrEmpty(input.UserName))
            {
                sqlBuilder.AppendLine($"AND q.{propertyColumnMap["CheckName"]} LIKE :UserName");
                parameters.Add(new OracleParameter("UserName", $"%{input.UserName}%"));
            }

            if (!string.IsNullOrEmpty(input.DangerType))
            {
                sqlBuilder.AppendLine($"AND q.{propertyColumnMap["DangerType"]} LIKE :DangerType");
                parameters.Add(new OracleParameter("DangerType", $"%{input.DangerType}%"));
            }

            if (!string.IsNullOrEmpty(input.DangerTypeCode))
            {
                sqlBuilder.AppendLine($"AND q.{propertyColumnMap["DangerTypeCode"]} = :DangerTypeCode");
                parameters.Add(new OracleParameter("DangerTypeCode", input.DangerTypeCode));
            }

            if (!string.IsNullOrEmpty(input.Status))
            {
                sqlBuilder.AppendLine($"AND q.{propertyColumnMap["Status"]} = :Status");
                parameters.Add(new OracleParameter("Status", input.Status));
            }

            if (!string.IsNullOrEmpty(input.CheckOrganizationName))
            {
                var corg = await _context.Set<QHSESysOrganization>().FindAsync(input.CheckOrganizationId);
                if (corg != null)
                {
                    var escapedNamePath = EscapeLikePattern(corg.NamePath);
                    sqlBuilder.AppendLine($"AND EXISTS (" +
                        $"SELECT 1 " +
                        $"FROM {tableNameOrg} q2 " +
                        $"WHERE q2.{propertyColumnMapOrg["OrganizationId"]} = q.{propertyColumnMap["OrganizationId"]} " +
                        $"AND q2.NAME_PATH LIKE '{escapedNamePath}%' ESCAPE '/')");
                }
            }

            // Getting total count
            var countSql = $"SELECT COUNT(*) FROM ({sqlBuilder})";
            var totalRows = await _customDbContext.ExecuteScalarAsync(countSql, parameters.ToArray());

            // Adding pagination
            sqlBuilder.AppendLine($"ORDER BY q.{propertyColumnMap["CheckDate"]} DESC");
            sqlBuilder.AppendLine($"OFFSET {(input.PageNo - 1) * input.PageSize} ROWS FETCH NEXT {input.PageSize} ROWS ONLY");
            var finalQuery = sqlBuilder.ToString();
            Console.WriteLine("Executing SQL Query: " + finalQuery);

            // Execute the query and map the results
            var dangers = await _customDbContext.ExecuteSqlQueryAsync(
                finalQuery,
                reader => new QHSEDangerModel
                {
                    qhseXyDangerId = reader.GetString(reader.GetOrdinal("qhseXyDangerId")),
                    UserId = reader.GetString(reader.GetOrdinal("UserId")),
                    WorkItem = reader.GetString(reader.GetOrdinal("WorkItem")),
                    CheckName = reader.GetString(reader.GetOrdinal("CheckName")),
                    CheckDate = reader.GetDateTime(reader.GetOrdinal("CheckDate")),
                    OrganizationName = reader.GetString(reader.GetOrdinal("OrganizationName")),
                    Location = reader.GetString(reader.GetOrdinal("Location")),
                    DangerType = reader.GetString(reader.GetOrdinal("DangerType")),
                    Description = reader.GetString(reader.GetOrdinal("Description")),
                    CheckOrganizationId = reader.GetString(reader.GetOrdinal("CheckOrganizationId")),
                    Status = reader.GetString(reader.GetOrdinal("Status"))
                },
                parameters.ToArray());

            result = new PageResultModel<QHSEDangerModel>
            {
                TotalRows = totalRows,
                Rows = dangers,
                PageNo = input.PageNo,
                PageSize = input.PageSize
            };
        }

        result.SetPage(input);
        result.CountTotalPage();

        return result;
    }

    static string RemoveHyphens(Guid guid)
    {
        return guid.ToString().Replace("-", ""); // 通过 Replace 函数移除连字符 "-"
    }
    public async Task<List<(QHSEDangerModel models,string res)>> AddBatchAsync(List<QHSEDangerModel> model) 
    {
        List<(QHSEDangerModel models, string res)> reses = new List<(QHSEDangerModel models, string res)>();
        int count = 0;
        foreach (var modelItem in model)
        {
            count++;
            modelItem.EnterTime = DateTime.Now;
           var res = await Add1Async(modelItem);
            if (res == "请完善信息")
            {
                reses.Add((modelItem,res));
            }
            else
            {
                reses.Add((modelItem, res));
            }
        }
        await _cacheService.ClearDangerCacheAsync();
        return reses;
    }
    public async Task<string> AddAsync(QHSEDangerModel model)
    {
        using (var transaction = await _context.Database.BeginTransactionAsync())
        {
            try
            {
                if (string.IsNullOrEmpty(model.qhseXyDangerId))
                {
                    Guid guid = Guid.NewGuid();
                    string guidString = RemoveHyphens(guid);

                    model.qhseXyDangerId = guidString;
                }
                var hashList = new List<string>(); // 用于存储每个文件的哈希值
                if (model.DangerFiles.Count != 0)
                {
                    foreach (var file in model.DangerFiles)
                    {
                        using (var sha256 = SHA256.Create())
                        {
                            byte[] byteArray = Convert.FromBase64String(file.Base64Content);
                            byte[] hash = sha256.ComputeHash(byteArray);
                            string hashString = BitConverter.ToString(hash).Replace("-", "").ToLower();
                            hashList.Add(hashString);
                        }
                    }
                    var dangermsg = await utilService.UploadFilesAsync( "DANGER",model.DangerFiles, model.UserId,_qhseFileService);
                    if (dangermsg.Count != 0)
                    {
                        model.DangerPic = dangermsg.LastOrDefault().ArgumentName;
                    }
                }
                if (model.ReformerFiles.Count != 0)
                {
                    foreach (var file in model.ReformerFiles)
                    {
                        using (var sha256 = SHA256.Create())
                        {
                            byte[] byteArray = Convert.FromBase64String(file.Base64Content);
                            byte[] hash = sha256.ComputeHash(byteArray);
                            string hashString = BitConverter.ToString(hash).Replace("-", "").ToLower();
                            hashList.Add(hashString);
                        }
                    }
                    var msg = await utilService.UploadFilesAsync("REFORMER",model.ReformerFiles, model.UserId, _qhseFileService);
                    if (msg.Count != 0)
                    {
                        model.ReformerPic = msg.LastOrDefault().ArgumentName;
                    }
                }
                model.ImageHash = string.Join("+", hashList); // 拼接哈希值字符串
                var danger = MapperHelper.Map<QHSESysDanger>(model);
                danger.EnterTime = DateTime.Now;
                await _context.AddAsync(danger);
                await _context.SaveChangesAsync();

                _context.ChangeTracker.Clear();

                // Retrieve the record with the largest numeric ID
                //var latestDanger = await _context.Set<QHSESysDanger>()
                //    .Where(u => u.UserId == danger.UserId)
                //   .OrderByDescending(x => Convert.ToInt32(x.QhseXyDangerId))
                //   .Select(u => u.QhseXyDangerId)
                //   .FirstOrDefaultAsync();
                if (model.Push && model.DangerType != "危害因素")
                {
                    var msg = new DangerMsg();
                    msg.Id = model.qhseXyDangerId;
                    msg.OrgId = model.SecondOrganizationId;
                    msg.Time = model.EnterTime;
                    msg.Type = model.DangerType;
                    utilService.PushDanger(msg);
                }
                // Clear cache
                await _cacheService.ClearDangerCacheAsync();

                await transaction.CommitAsync(); // Commit the transaction

                if (model.qhseXyDangerId != null)
                {
                    return model.qhseXyDangerId;
                }
                else
                {
                    return null; // or throw an exception, depending on your requirements
                }
            }
            catch (Exception)
            {
                await transaction.RollbackAsync(); // Rollback the transaction if an exception occurs
                throw;
            }
        }
    }
    public async Task<string> Add1Async(QHSEDangerModel model)
    {
        using (var transaction = await _context.Database.BeginTransactionAsync())
        {
            try
            {
                if (string.IsNullOrEmpty(model.qhseXyDangerId))
                {
                    Guid guid = Guid.NewGuid();
                    string guidString = RemoveHyphens(guid);

                    model.qhseXyDangerId = guidString;
                }
                var hashList = new List<string>(); // 用于存储每个文件的哈希值
                if (model.DangerFiles.Count != 0)
                {
                    foreach (var file in model.DangerFiles)
                    {
                        using (var sha256 = SHA256.Create())
                        {
                            byte[] byteArray = Convert.FromBase64String(file.Base64Content);
                            byte[] hash = sha256.ComputeHash(byteArray);
                            string hashString = BitConverter.ToString(hash).Replace("-", "").ToLower();
                            hashList.Add(hashString);
                        }
                    }
                    var dangermsg = await utilService.UploadFilesAsync("DANGER", model.DangerFiles, model.UserId, _qhseFileService);
                    model.DangerPic = dangermsg.LastOrDefault().ArgumentName;
                }

                if (model.ReformerFiles.Count != 0)
                {
                    foreach (var file in model.ReformerFiles)
                    {
                        using (var sha256 = SHA256.Create())
                        {
                            byte[] byteArray = Convert.FromBase64String(file.Base64Content);
                            byte[] hash = sha256.ComputeHash(byteArray);
                            string hashString = BitConverter.ToString(hash).Replace("-", "").ToLower();
                            hashList.Add(hashString);
                        }
                    }
                    var msg = await utilService.UploadFilesAsync("REFORMER", model.ReformerFiles, model.UserId, _qhseFileService);
                    model.ReformerPic = msg.LastOrDefault().ArgumentName;
                }
                model.ImageHash = string.Join("+", hashList); // 拼接哈希值字符串
                var danger = MapperHelper.Map<QHSESysDanger>(model);
                if (IsEmpty(danger))
                {
                    return "请完善信息";
                }
                if (string.IsNullOrEmpty(danger.SecondOrganizationId))
                {
                   var user =  await _context.Set<QHSESysLogin>().FindAsync(danger.UserId);
                    if (user != null)
                    {
                        danger.SecondOrganizationId = user.SecondOrganizationId;
                        danger.ThirdOrganizationId = user.ThirdOrganizationId;
                    }
                }
                await _context.AddAsync(danger);
                await _context.SaveChangesAsync();
                _context.ChangeTracker.Clear();
                // Retrieve the record with the largest numeric ID
                //var latestDanger = await _context.Set<QHSESysDanger>()
                //    .Where(u => u.UserId == danger.UserId)
                //   .OrderByDescending(x => Convert.ToInt32(x.QhseXyDangerId))
                //   .Select(u => u.QhseXyDangerId)
                //   .FirstOrDefaultAsync();
                // Clear cache
                await _cacheService.ClearDangerCacheAsync();

                await transaction.CommitAsync(); // Commit the transaction

                if (model.qhseXyDangerId != null)
                {
                    return model.qhseXyDangerId;
                }
                else
                {
                    return null; // or throw an exception, depending on your requirements
                }
            }
            catch (Exception)
            {
                await transaction.RollbackAsync(); // Rollback the transaction if an exception occurs
                throw;
            }
        }
    }
    public static bool IsEmpty(object obj)
    {
        Type type = obj.GetType();
        PropertyInfo[] properties = type.GetProperties();

        foreach (PropertyInfo property in properties)
        {
            object value = property.GetValue(obj);
            var s = property.ToString();
            if (property.ToString() != "System.String QhseXyDangerId" && property.ToString() != "System.String ObserveComm" && property.ToString() != "System.String PotentialConsequence" && property.ToString() != "System.String DangerPic" && property.ToString() != "System.String ReformerPic" && property.ToString() != "System.String CheckDescription"
                && property.ToString() != "System.Nullable`1[System.DateTime] AcceptanceDate" && property.ToString() != "System.String ImageHash" && property.ToString() != "System.String WorkType" && property.ToString() != "System.String WorkContent" &&
                property.ToString() != "System.String WorkRisk" && property.ToString() != "System.String KeyWorkCode" && property.ToString() != "System.String ErpNo"
                && property.ToString() != "System.String DevName" && property.ToString() != "System.String UpdateBy" && property.ToString() != "System.Nullable`1[System.DateTime] UpdateTime"
                && property.ToString() != "System.String SpecMod" && property.ToString() != "System.String SecondOrganizationId"
                && property.ToString() != "System.String Management" && property.ToString() != "System.String VideoUrl" && property.ToString() != "System.String InspectionMethod"
                 && property.ToString() != "System.String ThirdOrganizationId" && (value == null || string.IsNullOrEmpty(value.ToString())))
            {
                return true;
            }
        }

        return false;
    }
    public async Task<bool> IsImageDuplicate(string newImageBase64)
    {
        if (string.IsNullOrEmpty(newImageBase64))
        {
            return false;
        }

        using (var sha256 = SHA256.Create())
        {
            byte[] byteArray = Convert.FromBase64String(newImageBase64);
            byte[] hash = sha256.ComputeHash(byteArray);
            string hashString = BitConverter.ToString(hash).Replace("-", "").ToLower();

            DateTime oneYearAgo = DateTime.Today.AddYears(-1);

            var duplicate = await _context.Set<QHSESysDanger>()
                .Where(danger => danger.CheckDate >= oneYearAgo && danger.ImageHash.Contains(hashString))
                .AnyAsync();

            return duplicate;
        }
    }
    public async Task<QHSEDangerModel> QueryDetailAsync(string Did)
    {
        var danger  = await _context.Set<QHSESysDanger>().AsNoTracking().FirstOrDefaultAsync(u=>u.QhseXyDangerId ==Did);
        if (danger == null)
        {
            return null;
        }
        var model = MapperHelper.Map<QHSEDangerModel>(danger);
        if (model == null)
        {
            return null;
        }
        if (model.DangerPic != null)
        {
            var dangerfiles = await utilService.GetFilesAsync(model.DangerPic, _qhseFileService);
            model.DangerFiles = dangerfiles;
        }
        if (model.ReformerPic != null)
        {
            var reformerfiles = await utilService.GetFilesAsync(model.ReformerPic, _qhseFileService);
            model.ReformerFiles = reformerfiles;
        }
        return model;
    }
    public async Task<QHSEDangerModel> QueryDetailCacheAsync(string Did)
    {
        var str = HashHelper.DecryptAndExtractIdAndTime(Did, "gmh");
        if (DateTime.Now >= str.time.AddMinutes(120))
        {
            return null;
        }

        var danger = await _context.Set<QHSESysDanger>().FindAsync(str.id);
        if (danger == null)
        {
            return null;
        }

        var model = MapperHelper.Map<QHSEDangerModel>(danger);

        if (!string.IsNullOrEmpty(model.DangerPic))
        {
            model.DangerFiles = await utilService.GetFilesAsync(model.DangerPic, _qhseFileService);
        }

        if (!string.IsNullOrEmpty(model.ReformerPic))
        {
            model.ReformerFiles = await utilService.GetFilesAsync(model.ReformerPic, _qhseFileService);
        }

        return model;
    }
    //直接访问
    public async Task<QHSEDangerModel> QueryDetailAsyncSQL(string Did)
    {
        using (var connection = new OracleConnection(_dbConfig.GetConnectionString()))
        {
            connection.Open();
            // 使用参数化的 SQL 语句
            string query = "SELECT * FROM QHSE_XY_DANGER WHERE QHSE_XY_DANGER_ID =:Did";

            using (var command = new OracleCommand(query, connection))
            {
                // 添加参数
                command.Parameters.Add(new OracleParameter("Did", Did));

                using (var reader = command.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        QHSEDangerModel model =
                         new QHSEDangerModel
                         {
                             qhseXyDangerId = reader.IsDBNull(reader.GetOrdinal("QHSE_XY_DANGER_ID")) ? "" : reader.GetString(reader.GetOrdinal("QHSE_XY_DANGER_ID")),
                             CheckType = reader.IsDBNull(reader.GetOrdinal("CHECK_TYPE")) ? "" : reader.GetString(reader.GetOrdinal("CHECK_TYPE")),
                             CheckOrganizationId = reader.IsDBNull(reader.GetOrdinal("CHECKORGANIZATIONID")) ? null : reader.GetString(reader.GetOrdinal("CHECKORGANIZATIONID")),
                             EnterTime = reader.IsDBNull(reader.GetOrdinal("ENTER_DATE")) ? (DateTime?)null : reader.GetDateTime(reader.GetOrdinal("ENTER_DATE")),
                             EnterSys = reader.IsDBNull(reader.GetOrdinal("ENTER_SYS")) ? null : reader.GetString(reader.GetOrdinal("ENTER_SYS")),
                             UserId = reader.IsDBNull(reader.GetOrdinal("USER_ID")) ? "" : reader.GetString(reader.GetOrdinal("USER_ID")),
                             CheckName = reader.IsDBNull(reader.GetOrdinal("CHECK_NAME")) ? "" : reader.GetString(reader.GetOrdinal("CHECK_NAME")),
                             CheckDate = reader.IsDBNull(reader.GetOrdinal("CHECK_DATE")) ? default : reader.GetDateTime(reader.GetOrdinal("CHECK_DATE")),
                             OrganizationId = reader.IsDBNull(reader.GetOrdinal("ORGANIZATION_ID")) ? "" : reader.GetString(reader.GetOrdinal("ORGANIZATION_ID")),
                             OrganizationName = reader.IsDBNull(reader.GetOrdinal("ORGANIZATION_NAME")) ? "" : reader.GetString(reader.GetOrdinal("ORGANIZATION_NAME")),
                             WorkItem = reader.IsDBNull(reader.GetOrdinal("WORK_ITEM")) ? "" : reader.GetString(reader.GetOrdinal("WORK_ITEM")),
                             WorkSpecialty = reader.IsDBNull(reader.GetOrdinal("WORK_SPECIALTY")) ? "" : reader.GetString(reader.GetOrdinal("WORK_SPECIALTY")),
                             WorkType = reader.IsDBNull(reader.GetOrdinal("WORK_TYPE")) ? null : reader.GetString(reader.GetOrdinal("WORK_TYPE")),
                             WorkContent = reader.IsDBNull(reader.GetOrdinal("WORK_CONTENT")) ? null : reader.GetString(reader.GetOrdinal("WORK_CONTENT")),
                             WorkRisk = reader.IsDBNull(reader.GetOrdinal("WORK_RISK")) ? null : reader.GetString(reader.GetOrdinal("WORK_RISK")),
                             Location = reader.IsDBNull(reader.GetOrdinal("LOCATION")) ? "" : reader.GetString(reader.GetOrdinal("LOCATION")),
                             DangerTypeCategory = reader.IsDBNull(reader.GetOrdinal("DANGER_TYPE_CATEGORY")) ? null : reader.GetString(reader.GetOrdinal("DANGER_TYPE_CATEGORY")),
                             DangerType = reader.IsDBNull(reader.GetOrdinal("DANGER_TYPE")) ? "" : reader.GetString(reader.GetOrdinal("DANGER_TYPE")),
                             Description = reader.IsDBNull(reader.GetOrdinal("DESCRIPTION")) ? "" : reader.GetString(reader.GetOrdinal("DESCRIPTION")),
                             DangerCategory = reader.IsDBNull(reader.GetOrdinal("DANGER_CATEGORY")) ? "" : reader.GetString(reader.GetOrdinal("DANGER_CATEGORY")),
                             DangerClause = reader.IsDBNull(reader.GetOrdinal("DANGER_CLAUSE")) ? null : reader.GetString(reader.GetOrdinal("DANGER_CLAUSE")),
                             HseElement = reader.IsDBNull(reader.GetOrdinal("HSE_ELEMENT")) ? "" : reader.GetString(reader.GetOrdinal("HSE_ELEMENT")),
                             ObserveComm = reader.IsDBNull(reader.GetOrdinal("OBSERVE_COMM")) ? null : reader.GetString(reader.GetOrdinal("OBSERVE_COMM")),
                             CauseAnalysis = reader.IsDBNull(reader.GetOrdinal("CAUSE_ANALYSIS")) ? "" : reader.GetString(reader.GetOrdinal("CAUSE_ANALYSIS")),
                             DirectDepartment = reader.IsDBNull(reader.GetOrdinal("DIRECT_DEPARTMENT")) ? "" : reader.GetString(reader.GetOrdinal("DIRECT_DEPARTMENT")),
                             PotentialConsequence = reader.IsDBNull(reader.GetOrdinal("POTENTIAL_CONSEQUENCE")) ? null : reader.GetString(reader.GetOrdinal("POTENTIAL_CONSEQUENCE")),
                             DangerPic = reader.IsDBNull(reader.GetOrdinal("DANGER_PIC")) ? null : reader.GetString(reader.GetOrdinal("DANGER_PIC")),
                             ReformerUserId = reader.IsDBNull(reader.GetOrdinal("REFORMER_USER_ID")) ? "" : reader.GetString(reader.GetOrdinal("REFORMER_USER_ID")),
                             ReformerUserName = reader.IsDBNull(reader.GetOrdinal("REFORMER_USER_NAME")) ? "" : reader.GetString(reader.GetOrdinal("REFORMER_USER_NAME")),
                             ReformerDate = reader.IsDBNull(reader.GetOrdinal("REFORMER_DATE")) ? default : reader.GetDateTime(reader.GetOrdinal("REFORMER_DATE")),
                             ReformerPic = reader.IsDBNull(reader.GetOrdinal("REFORMER_PIC")) ? null : reader.GetString(reader.GetOrdinal("REFORMER_PIC")),
                             Status = reader.IsDBNull(reader.GetOrdinal("STATUS")) ? null : reader.GetString(reader.GetOrdinal("STATUS")),
                             AcceptanceDate = reader.IsDBNull(reader.GetOrdinal("ACCEPTANCE_DATE")) ? (DateTime?)null : reader.GetDateTime(reader.GetOrdinal("ACCEPTANCE_DATE")),
                             CheckDescription = reader.IsDBNull(reader.GetOrdinal("CHECK_DESCRIPTION")) ? null : reader.GetString(reader.GetOrdinal("CHECK_DESCRIPTION")),
                             ImageHash = reader.IsDBNull(reader.GetOrdinal("IMAGE_HASH")) ? null : reader.GetString(reader.GetOrdinal("IMAGE_HASH")),
                             DangerCateId = reader.IsDBNull(reader.GetOrdinal("DANGERCATE_ID")) ? null : reader.GetString(reader.GetOrdinal("DANGERCATE_ID")),
                             CheckTypeCode = reader.IsDBNull(reader.GetOrdinal("CHECKTYPE_CODE")) ? null : reader.GetString(reader.GetOrdinal("CHECKTYPE_CODE")),
                             KeyWorkCode = reader.IsDBNull(reader.GetOrdinal("KEYWORK_CODE")) ? null : reader.GetString(reader.GetOrdinal("KEYWORK_CODE")),
                             WorkSpecialtyCode = reader.IsDBNull(reader.GetOrdinal("WORKSPECIALTY_CODE")) ? null : reader.GetString(reader.GetOrdinal("WORKSPECIALTY_CODE")),
                             DangerTypeCode = reader.IsDBNull(reader.GetOrdinal("DANGERTYPE_CODE")) ? null : reader.GetString(reader.GetOrdinal("DANGERTYPE_CODE")),
                             HseCode = reader.IsDBNull(reader.GetOrdinal("HSE_CODE")) ? null : reader.GetString(reader.GetOrdinal("HSE_CODE")),
                             DepartmentCode = reader.IsDBNull(reader.GetOrdinal("DEPARTMENT_CODE")) ? null : reader.GetString(reader.GetOrdinal("DEPARTMENT_CODE")),
                             ClauseAnalysisCode = reader.IsDBNull(reader.GetOrdinal("CLAUSEANALYSIS_CODE")) ? null : reader.GetString(reader.GetOrdinal("CLAUSEANALYSIS_CODE")),
                             ErpNo = reader.IsDBNull(reader.GetOrdinal("ERP_NO")) ? null : reader.GetString(reader.GetOrdinal("ERP_NO")),
                             DevName = reader.IsDBNull(reader.GetOrdinal("DEV_NAME")) ? null : reader.GetString(reader.GetOrdinal("DEV_NAME")),
                             SpecMod = reader.IsDBNull(reader.GetOrdinal("SPEC_MOD")) ? null : reader.GetString(reader.GetOrdinal("SPEC_MOD")),
                             SecondOrganizationId = reader.IsDBNull(reader.GetOrdinal("SECOND_ORGANIZATIONID")) ? null : reader.GetString(reader.GetOrdinal("SECOND_ORGANIZATIONID")),
                             ThirdOrganizationId = reader.IsDBNull(reader.GetOrdinal("THIRD_ORGANIZATIONID")) ? null : reader.GetString(reader.GetOrdinal("THIRD_ORGANIZATIONID")),
                             UpdateBy = reader.IsDBNull(reader.GetOrdinal("UPDATE_BY")) ? null : reader.GetString(reader.GetOrdinal("UPDATE_BY")),
                             UpdateTime = reader.IsDBNull(reader.GetOrdinal("UPDATE_TIME")) ? (DateTime?)null : reader.GetDateTime(reader.GetOrdinal("UPDATE_TIME")),
                             Management = reader.IsDBNull(reader.GetOrdinal("MANAGEMENT")) ? null : reader.GetString(reader.GetOrdinal("MANAGEMENT")),
                             VideoUrl = reader.IsDBNull(reader.GetOrdinal("VIDEO_URL")) ? null : reader.GetString(reader.GetOrdinal("VIDEO_URL")),
                             InspectionMethod = reader.IsDBNull(reader.GetOrdinal("INSPECTION_METHOD")) ? null : reader.GetString(reader.GetOrdinal("INSPECTION_METHOD"))
                         };
                        //加入文件
                        if (model.DangerPic != null)
                        {
                            var dangerfiles = await utilService.GetFilesAsync(model.DangerPic, _qhseFileService);
                            model.DangerFiles = dangerfiles;
                        }
                        if (model.ReformerPic != null)
                        {
                            var reformerfiles = await utilService.GetFilesAsync(model.ReformerPic, _qhseFileService);
                            model.ReformerFiles = reformerfiles;
                        }
                        return model;
                    }
                    else
                    {
                        // 如果没有找到记录，则返回 null
                        return null;
                    }
                }
            }
        }
    }

    public async Task<int> DeleteAsync(IEnumerable<string> ids)
    {
        var dangers = await _context.Set<QHSESysDanger>()
            .Where(a => ids.Contains(a.QhseXyDangerId))
            .ToListAsync();
        foreach (var danger in dangers)
        {
            if (danger.DangerPic != null)
            {
                List<string> namesDangerList = danger.DangerPic.Split(", ")
                                    .Select(name => name.Trim())  // 去除每个名字前后的空格
                                    .ToList();
                foreach (var name in namesDangerList)
                {
                    _qhseFileService.RemoveAsync("ssew", name);
                }
            }
            if (danger.ReformerPic != null)
            {
                List<string> namesReformerList = danger.ReformerPic.Split(", ")
                                    .Select(name => name.Trim())  // 去除每个名字前后的空格
                                    .ToList();
                foreach (var name in namesReformerList)
                {
                    _qhseFileService.RemoveAsync("ssew", name);
                }
            }
        }
        _context.RemoveRange(dangers);
        //清缓存
        await _cacheService.ClearDangerCacheAsync();
        await _cacheService.ClearDTotalCacheAsync();
        int ret = await _context.SaveChangesAsync();
        return ret;
    }
    public async Task<int> UpdateAsync(QHSEDangerModel model)
    {
        var danger = await _context.Set<QHSESysDanger>()
            .FindAsync(model.qhseXyDangerId);

        if (danger == null)
        {
            throw AppResultException.Status404NotFound("找不到隐患，更新失败");
        }
        var hashList = new List<string>(); // 用于存储每个文件的哈希值
        if (model.DangerFiles.Count != 0)
        {
            var fileUploadItems = model.DangerFiles.Select(file => new FileUploadItem
            {
                Name = file.Name,
                Base64Content = file.Base64Content
            }).ToList();
            var msgs = await utilService.UploadFilesAsync("DANGER", fileUploadItems, danger.UserId, _qhseFileService);
            bool uploadSuccess = msgs.All(msg => msg.FileStatus == 1);

            if (uploadSuccess)
            {
                if (!string.IsNullOrEmpty(danger.DangerPic))
                {
                    List<string> namesList = danger.DangerPic.Split(", ")
                                     .Select(name => name.Trim())  // 去除每个名字前后的空格
                                     .ToList();
                    foreach (var name in namesList)
                    {
                        _qhseFileService.RemoveAsync("ssew", name);
                    }
                }

                model.DangerPic = msgs.LastOrDefault()?.FileName;
            }
        }
        else
        {
            if (!string.IsNullOrEmpty(danger.DangerPic))
            {
                List<string> namesList = danger.DangerPic.Split(", ")
                                        .Select(name => name.Trim())  // 去除每个名字前后的空格
                                        .ToList();
                foreach (var name in namesList)
                {
                    _qhseFileService.RemoveAsync("ssew", name);
                }
            }

            model.DangerPic = null;
        }
        if (model.ReformerFiles.Count != 0)
        {
            var fileUploadItems = model.ReformerFiles.Select(file => new FileUploadItem
            {
                Name = file.Name,
                Base64Content = file.Base64Content
            }).ToList();
            var msgs = await utilService.UploadFilesAsync("REFORMER", fileUploadItems, danger.UserId, _qhseFileService);
            bool uploadSuccess = msgs.All(msg => msg.FileStatus == 1);

            if (uploadSuccess)
            {
                if (!string.IsNullOrEmpty(danger.ReformerPic))
                {
                    List<string> namesList = danger.ReformerPic.Split(", ")
                                     .Select(name => name.Trim())  // 去除每个名字前后的空格
                                     .ToList();
                    foreach (var name in namesList)
                    {
                        _qhseFileService.RemoveAsync("ssew", name);
                    }
                }

                model.ReformerPic = msgs.LastOrDefault()?.FileName;
            }
        }
        else
        {
            if (!string.IsNullOrEmpty(danger.ReformerPic))
            {
                List<string> namesList = danger.ReformerPic.Split(", ")
                                        .Select(name => name.Trim())  // 去除每个名字前后的空格
                                        .ToList();
                foreach (var name in namesList)
                {
                    _qhseFileService.RemoveAsync("ssew", name);
                }
            }

            model.ReformerPic = null;
        }
        MapperHelper.Map<QHSEDangerModel, QHSESysDanger>(model, danger);
        _context.Update(danger);
        // 清缓存
        await _cacheService.ClearDangerCacheAsync();
        int ret = await _context.SaveChangesAsync();
        if (ret == 0)
        {
            throw AppResultException.Status200OK("更新记录数为0");
        }
        return ret;
    }
    public async Task<int> UpdateManagementAsync(QHSEDangerManagementModel model)
    {
        var danger = await _context.Set<QHSESysDanger>()
            .FindAsync(model.QhseDangerId);

        if (danger == null)
        {
            throw AppResultException.Status404NotFound("找不到隐患，更新失败");
        }
        danger.Management = model.Management;
        _context.Update(danger);
        // 清缓存
        await _cacheService.ClearDangerCacheAsync();
        int ret = await _context.SaveChangesAsync();
        if (ret == 0)
        {
            throw AppResultException.Status200OK("更新记录数为0");
        }
        return ret;
    }
    public async Task<PageResultModel<QHSEDangerModel>> ComGetPageAsync(ComDangerPageInputModel input)
    {
        var result = new PageResultModel<QHSEDangerModel>();
        if (!string.IsNullOrEmpty(input.id))
        {
            var danger = await _context.Set<QHSESysDanger>().AsNoTracking().Where(u => u.QhseXyDangerId == input.id).Join(_context.Set<QHSESysOrganization>(),
                            danger => danger.OrganizationId,
                            organization => organization.OrganizationId,
                            (danger, organization) => new { Danger = danger, Organization = organization })
                            .Join(_context.Set<QHSESysOrganization>(),
                                dangerUserOrganization => dangerUserOrganization.Danger.CheckOrganizationId,
                                organization => organization.OrganizationId,
                                (dangerUserOrganization, organization) => new {
                                    Danger = dangerUserOrganization.Danger,
                                    OrganizationName = dangerUserOrganization.Organization.NamePath,
                                    CheckOrganizationName = organization.NamePath,
                                    CheckOrganizationId = organization.OrganizationId
                                })
                            .Select(joinResult => new QHSEDangerModel
                            {
                                qhseXyDangerId = joinResult.Danger.QhseXyDangerId,
                                CheckType = joinResult.Danger.CheckType,
                                CheckName = joinResult.Danger.CheckName,
                                CheckDate = joinResult.Danger.CheckDate,
                                OrganizationName = joinResult.OrganizationName,
                                CheckOrganizationName = joinResult.CheckOrganizationName,
                                CheckOrganizationId = joinResult.CheckOrganizationId,
                                WorkItem = joinResult.Danger.WorkItem,
                                Location = joinResult.Danger.Location,
                                DangerType = joinResult.Danger.DangerType,
                                Description = joinResult.Danger.Description,
                                Status = joinResult.Danger.Status,
                                DangerCategory = joinResult.Danger.DangerCategory,
                                EnterSys = joinResult.Danger.EnterSys,
                                WorkRisk = joinResult.Danger.WorkRisk,
                                WorkType = joinResult.Danger.WorkType,
                                WorkSpecialty = joinResult.Danger.WorkSpecialty,
                                ErpNo = joinResult.Danger.ErpNo,
                                EnterTime = joinResult.Danger.EnterTime,
                                SecondOrganizationId = joinResult.Danger.SecondOrganizationId
                            })
                            .ToListAsync();
            var idResult = new PageResultModel<QHSEDangerModel>
            {
                TotalRows = 1,
                Rows = danger,
                PageNo = input.PageNo,
                PageSize = input.PageSize
            };
            return idResult;
        }
        if (utilService.isEmpty(input))
        {
            return result;
        }
        var query = _context.Set<QHSESysDanger>().AsNoTracking().Where(u => u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd);
        if (!string.IsNullOrEmpty(input.DangerOrganizationName) && input.DangerOrganizationId != "00")
        {
            var org = await _context.Set<QHSESysOrganization>().FindAsync(input.DangerOrganizationId);
            if (org != null)
            {
                var escapedNamePath = EscapeLikePattern(org.NamePath);
                query = query
                .Join(_context.Set<QHSESysOrganization>(), // Join with Organization table
                    danger => danger.OrganizationId,
                    organization => organization.OrganizationId,
                    (danger, organization) => new { Danger = danger, Organization = organization })
                .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedNamePath}%", "/"))
                .Select(joinResult => joinResult.Danger);
            }
        }
        if (input.CheckTypes.Count != 0)
        {
            query = query.Where(u => input.CheckTypes.Any(t => t == u.CheckType));
        }
        if (!string.IsNullOrEmpty(input.CheckOrganizationName) && input.CheckOrganizationId != "00")
        {
            var corg = await _context.Set<QHSESysOrganization>().FindAsync(input.CheckOrganizationId);
            if (corg != null)
            {
                var escapedcNamePath = EscapeLikePattern(corg.NamePath);
                query = query
                    .Join(_context.Set<QHSESysOrganization>(), // Join with QHSEOrganization table
                        joinResult => joinResult.CheckOrganizationId,
                        organization => organization.OrganizationId,
                        (joinResult, organization) => new { Danger = joinResult, Organization = organization })
                    .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedcNamePath}%", "/"))
                    .Select(joinResult => joinResult.Danger);
            }
        }
        if (!string.IsNullOrEmpty(input.DangerCategory))
        {
            var cate = await _categoryService.GetChildren(input.DangerCategory);
            if (cate.Count != 0)
            {
                query = query.Where(u => cate.Contains(u.DangerCategory));
            }
            else
            {
                query = query.Where(u => EF.Functions.Like(u.DangerCategory, $"%{input.DangerCategory}%"));
            }

        }

        if (!string.IsNullOrEmpty(input.UserName))
        {
            query = query.Where(u => EF.Functions.Like(u.CheckName, $"%{input.UserName}%"));
        }
        if (input.EnterSys.Count != 0)
        {
            query = query.Where(u => input.EnterSys.Contains(u.EnterSys));
        }


        if (input.Status.Count != 0)
        {
            query = query.Where(u => input.Status.Contains(u.Status));
        }

        if (input.WorkRisk.Count != 0)
        {
            query = query.Where(u => input.WorkRisk.Contains(u.WorkRisk));
        }

        if (input.WorkType.Count != 0)
        {
            query = query.Where(u => input.WorkType.Contains(u.WorkSpecialty + "-" + u.WorkType));
        }

        if (input.Location.Count != 0)
        {
            query = query.Where(u => input.Location.Contains(u.WorkSpecialty + "-" + u.Location));
        }

        if (input.WorkSpecialty.Count != 0)
        {
            string specialties = string.Join(",", input.WorkSpecialty);
            query = query.Where(u => specialties.Contains(u.WorkSpecialty));
        }

        if (input.DangerType.Count != 0)
        {
            query = query.Where(u => input.DangerType.Contains(u.DangerType));
        }
        if (!string.IsNullOrEmpty(input.ErpNo))
        {
            query = query.Where(u => input.ErpNo.Equals(u.ErpNo));
        }
        if (!string.IsNullOrEmpty(input.InspectionMethod))
        {
            query = query.Where(u => input.InspectionMethod.Equals(u.InspectionMethod));
        }
        var totalRows = await query.CountAsync();
        var dangers = await query
                    .Select(danger => new QHSEDangerModel
                    {
                        qhseXyDangerId = danger.QhseXyDangerId,
                        CheckType = danger.CheckType,
                        OrganizationName = _context.Set<QHSESysOrganization>()
                                              .Where(org => org.OrganizationId == danger.OrganizationId)
                                              .Select(org => org.NamePath)
                                              .FirstOrDefault(),
                        CheckName = danger.CheckName,
                        CheckOrganizationName = _context.Set<QHSESysOrganization>()
                                              .Where(org => org.OrganizationId == danger.CheckOrganizationId)
                                              .Select(org => org.NamePath)
                                              .FirstOrDefault(),
                        CheckOrganizationId = danger.CheckOrganizationId,
                        CheckDate = danger.CheckDate,
                        OrganizationId = danger.OrganizationId, // 获取 OrganizationName,
                        WorkItem = danger.WorkItem,
                        Location = danger.Location,
                        DangerType = danger.DangerType,
                        Description = danger.Description,
                        Status = danger.Status,
                        ErpNo = danger.ErpNo,
                        EnterTime = danger.EnterTime,
                        SecondOrganizationId = danger.SecondOrganizationId
                    })
                        .OrderByDescending(u => u.CheckDate)
                        .Skip((input.PageNo - 1) * input.PageSize)
                        .Take(input.PageSize)
                        .ToListAsync();
        result = new PageResultModel<QHSEDangerModel>
        {
            TotalRows = totalRows,
            Rows = dangers,
            PageNo = input.PageNo,
            PageSize = input.PageSize
        };
        result.SetPage(input);
        result.CountTotalPage();

        return result;
    }

    private IQueryable<QHSEDangerModel> GetBaseQuery(DbContext context,ComDangerPageInputModel input)
    {
        var query = context.Set<QHSESysDanger>().AsNoTracking();

        // 日期范围过滤
        if (input.TimeOn != null && input.TimeEnd != null)
        {
            query = query.Where(u => u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd);
        }

        // 危险组织名称过滤
        if (!string.IsNullOrEmpty(input.DangerOrganizationName) && input.DangerOrganizationId != "00")
        {
            var org = context.Set<QHSESysOrganization>().Find(input.DangerOrganizationId);
            if (org != null)
            {
                var escapedNamePath = EscapeLikePattern(org.NamePath);
                query = query
                    .Join(context.Set<QHSESysOrganization>(),
                        danger => danger.OrganizationId,
                        organization => organization.OrganizationId,
                        (danger, organization) => new { Danger = danger, Organization = organization })
                    .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                    .Select(joinResult => joinResult.Danger);
            }
        }

        // 检查类型过滤
        if (input.CheckTypes.Count != 0)
        {
            query = query.Where(u => input.CheckTypes.Contains(u.CheckType));
        }

        // 检查组织名称过滤
        if (!string.IsNullOrEmpty(input.CheckOrganizationName) && input.CheckOrganizationId != "00")
        {
            var corg = context.Set<QHSESysOrganization>().Find(input.CheckOrganizationId);
            if (corg != null)
            {
                var escapedcNamePath = EscapeLikePattern(corg.NamePath);
                query = query
                    .Join(context.Set<QHSESysOrganization>(),
                        danger => danger.CheckOrganizationId,
                        organization => organization.OrganizationId,
                        (danger, organization) => new { Danger = danger, Organization = organization })
                    .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedcNamePath}%", "/"))
                    .Select(joinResult => joinResult.Danger);
            }
        }

        // 危险类别过滤
        if (!string.IsNullOrEmpty(input.DangerCategory))
        {
            var cate = _categoryService.GetChildren(input.DangerCategory).Result;
            if (cate.Count != 0)
            {
                query = query.Where(u => cate.Contains(u.DangerCategory));
            }
            else
            {
                query = query.Where(u => EF.Functions.Like(u.DangerCategory, $"%{input.DangerCategory}%"));
            }
        }

        // 其他过滤条件
        if (!string.IsNullOrEmpty(input.UserName))
        {
            query = query.Where(u => EF.Functions.Like(u.CheckName, $"%{input.UserName}%"));
        }
        if (input.EnterSys.Count != 0)
        {
            query = query.Where(u => input.EnterSys.Contains(u.EnterSys));
        }
        if (input.Status.Count != 0)
        {
            query = query.Where(u => input.Status.Contains(u.Status));
        }
        if (input.WorkRisk.Count != 0)
        {
            query = query.Where(u => input.WorkRisk.Contains(u.WorkRisk));
        }
        if (input.WorkType.Count != 0)
        {
            query = query.Where(u => input.WorkType.Contains(u.WorkSpecialty + "-" + u.WorkType));
        }
        if (input.Location.Count != 0)
        {
            query = query.Where(u => input.Location.Contains(u.WorkSpecialty + "-" + u.Location));
        }
        if (input.WorkSpecialty.Count != 0)
        {
            query = query.Where(u => input.WorkSpecialty.Contains(u.WorkSpecialty));
        }
        if (input.DangerType.Count != 0)
        {
            query = query.Where(u => input.DangerType.Contains(u.DangerType));
        }
        if (!string.IsNullOrEmpty(input.ErpNo))
        {
            query = query.Where(u => u.ErpNo.Equals(input.ErpNo));
        }
        if (!string.IsNullOrEmpty(input.InspectionMethod))
        {
            query = query.Where(u => u.InspectionMethod.Equals(input.InspectionMethod));
        }
        var newquery = query.Join(context.Set<QHSESysOrganization>(),
                            danger => danger.OrganizationId,
                            organization => organization.OrganizationId,
                            (danger, organization) => new { Danger = danger, Organization = organization })
                            .Join(context.Set<QHSESysOrganization>(),
                                dangerUserOrganization => dangerUserOrganization.Danger.CheckOrganizationId,
                                organization => organization.OrganizationId,
                                (dangerUserOrganization, organization) => new
                                {
                                    Danger = dangerUserOrganization.Danger,
                                    OrganizationName = dangerUserOrganization.Organization.NamePath,
                                    CheckOrganizationName = organization.NamePath,
                                    CheckOrganizationId = organization.OrganizationId
                                })
                            .Select(joinResult => new QHSEDangerModel
                            {
                                qhseXyDangerId = joinResult.Danger.QhseXyDangerId,
                                CheckType = joinResult.Danger.CheckType,
                                CheckName = joinResult.Danger.CheckName,
                                CheckDate = joinResult.Danger.CheckDate,
                                OrganizationName = joinResult.OrganizationName,
                                CheckOrganizationName = joinResult.CheckOrganizationName,
                                WorkItem = joinResult.Danger.WorkItem,
                                WorkSpecialty = joinResult.Danger.WorkSpecialty,
                                WorkType = joinResult.Danger.WorkType,
                                WorkContent = joinResult.Danger.WorkContent,
                                WorkRisk = joinResult.Danger.WorkRisk,
                                Location = joinResult.Danger.Location,
                                DangerType = joinResult.Danger.DangerType,
                                Description = joinResult.Danger.Description,
                                DangerCategory = joinResult.Danger.DangerCategory,
                                HseElement = joinResult.Danger.HseElement,
                                CauseAnalysis = joinResult.Danger.CauseAnalysis,
                                DirectDepartment = joinResult.Danger.DirectDepartment,
                                PotentialConsequence = joinResult.Danger.PotentialConsequence,
                                ReformerUserName = joinResult.Danger.ReformerUserName,
                                ReformerDate = joinResult.Danger.ReformerDate,
                                Status = joinResult.Danger.Status,
                                AcceptanceDate = joinResult.Danger.AcceptanceDate,
                                CheckDescription = joinResult.Danger.CheckDescription,
                                ErpNo = joinResult.Danger.ErpNo
                            })
                            .OrderByDescending(u => u.CheckDate).AsQueryable();
        return newquery;
    }

    private async Task<int> GetTotalPageAsync(IQueryable<QHSEDangerModel> baseQuery)
    {
        var totalRows = await baseQuery.CountAsync();
        if(totalRows <2000) {
            return 1;
        }
        var pageSize = 2000;
        return totalRows/pageSize+1;
    }

    public async Task<byte[]> GenerateExcel(ComDangerPageInputModel model)
    {
        List<QHSEDangerModel> models = new List<QHSEDangerModel>();
        int totalPage = 0;


        // 创建单个 DbContext 实例
        using (var scope = _serviceScopeFactory.CreateScope())
        {
            var context = scope.ServiceProvider.GetRequiredService<SimpleDbContext>();

            try
            {
                // 获取总页数
                model.PageSize = 2000;
                var baseQuery = GetBaseQuery(context, model); // 使用相同的 context

                // 获取总页数
                totalPage = await GetTotalPageAsync(baseQuery);

                // 如果需要多线程处理分页查询，可以考虑用 Task 并发执行分页查询
                var tasks = new List<Task<List<QHSEDangerModel>>>();

                for (int pageNo = 1; pageNo <= totalPage; pageNo++)
                {
                    int currentPage = pageNo;

                    // 对每一页数据创建一个任务，每个任务使用独立的 DbContext 实例
                    tasks.Add(Task.Run(async () =>
                    {
                        using (var innerScope = _serviceScopeFactory.CreateScope())
                        {
                            var innerContext = innerScope.ServiceProvider.GetRequiredService<SimpleDbContext>();

                            var innerBaseQuery = GetBaseQuery(innerContext, model);

                            return await innerBaseQuery
                                .Skip((currentPage - 1) * model.PageSize)
                                .Take(model.PageSize)
                                .ToListAsync(); // 每个任务使用自己的 context
                        }
                    }));
                }

                // 等待所有任务完成
                var results = await Task.WhenAll(tasks);

                // 将所有分页数据汇总
                foreach (var result in results)
                {
                    models.AddRange(result);
                }
            }
            catch (Exception ex)
            {
                // 处理异常
                throw;
            }
        }
        // 设置 ExcelPackage 的 LicenseContext
        ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
        using (var package = new ExcelPackage())
        {
            var worksheet = package.Workbook.Worksheets.Add("Sheet1");

            // 设置表头并设置字体加粗
            for (int col = 0; col <= 21; col++)
            {
                ExcelRange headerCell = worksheet.Cells[1, col+1];
                headerCell.Value = GetHeaderTitle(col);
                headerCell.Style.Font.Bold = true;
            }

            // 填充数据
            for (int i = 0; i < models.Count; i++)
            {
                worksheet.Cells[i + 2, 1].Value = models[i].CheckOrganizationName;
                worksheet.Cells[i + 2, 2].Value = models[i].CheckName;
                worksheet.Cells[i + 2, 3].Value = models[i].CheckDate.ToString();
                worksheet.Cells[i + 2, 4].Value = models[i].CheckType;
                worksheet.Cells[i + 2, 5].Value = models[i].OrganizationName;
                worksheet.Cells[i + 2, 6].Value = models[i].WorkSpecialty;
                worksheet.Cells[i + 2, 7].Value = models[i].WorkItem;
                worksheet.Cells[i + 2, 8].Value = models[i].WorkType;
                worksheet.Cells[i + 2, 9].Value = models[i].WorkContent;
                worksheet.Cells[i + 2, 10].Value = models[i].WorkRisk;
                worksheet.Cells[i + 2, 11].Value = models[i].Location;
                worksheet.Cells[i + 2, 12].Value = models[i].DangerType;
                worksheet.Cells[i + 2, 13].Value = models[i].Description;
                worksheet.Cells[i + 2, 14].Value = models[i].DangerCategory;
                worksheet.Cells[i + 2, 15].Value = models[i].HseElement;
                worksheet.Cells[i + 2, 16].Value = models[i].CauseAnalysis;
                worksheet.Cells[i + 2, 17].Value = models[i].DirectDepartment;
                worksheet.Cells[i + 2, 18].Value = models[i].ReformerUserName;
                worksheet.Cells[i + 2, 19].Value = models[i].ReformerDate.ToString();
                worksheet.Cells[i + 2, 20].Value = models[i].Status;
                worksheet.Cells[i + 2, 21].Value = models[i].AcceptanceDate.ToString();
                worksheet.Cells[i + 2, 22].Value = models[i].CheckDescription;
                // 填充更多属性值...
            }

            // 自动调整列宽
            //worksheet.Cells[worksheet.Dimension.Address].AutoFitColumns();

            // 将 Excel 数据保存到字节数组
            var bytes = package.GetAsByteArray();


            return bytes;
        }
    }

    private string GetHeaderTitle(int column)
    {
        switch (column)
        {
            case 0:
                return "检查单位";
            case 1:
                return "检查人员";
            case 2:
                return "检查时间";
            case 3:
                return "检查类型";
            case 4:
                return "隐患单位";
            case 5:
                return "作业专业";
            case 6:
                return "作业项目";
            case 7:
                return "关键作业类型";
            case 8:
                return "关键作业内容";
            case 9:
                return "作业风险等级";
            case 10:
                return "位置区域";
            case 11:
                return "隐患级别";
            case 12:
                return "隐患描述";
            case 13:
                return "隐患类别";
            case 14:
                return "对应要素体系";
            case 15:
                return "原因分析";
            case 16:
                return "归属部门";
            case 17:
                return "整改负责人";
            case 18:
                return "限期整改时间";
            case 19:
                return "隐患状态";
            case 20:
                return "验收时间";
            case 21:
                return "验收描述";
            default:
                return string.Empty;
        }
    }

    public async Task<List<DangerAnalysisResult>> DangerCategoryAnalysisAsync(DangerCategoryInput input)
    {
        if (await _cacheService.GetTotalCategoryAnalysisAsync(input) != null)
        {
            return await _cacheService.GetTotalCategoryAnalysisAsync(input);
        }
        List<DangerAnalysisResult> result = new List<DangerAnalysisResult>();
        var query = _context.Set<QHSESysDanger>().AsNoTracking().AsQueryable();
        if (!string.IsNullOrEmpty(input.DangerCategory) && !string.IsNullOrEmpty(input.DangerOrganizationName) && input.TimeOn != null && input.TimeEnd != null && input.CheckType.Count != 0)
        {
            var org = await _context.Set<QHSESysOrganization>().FindAsync(input.DangerOrganizationId);
            var escapedNamePath = EscapeLikePattern(org.NamePath);
            query = query.Where(x =>   x.CheckDate >= input.TimeOn && x.CheckDate <= input.TimeEnd && input.CheckType.Contains(x.CheckType))
                    .Join(_context.Set<QHSESysOrganization>(), // Join with Organization table
                        danger => danger.OrganizationId,
                        organization => organization.OrganizationId,
                        (danger, organization) => new { Danger = danger, Organization = organization })
                    .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                    .Select(joinResult => joinResult.Danger);
            var newQuery =await query.Select(joinResult => joinResult.DangerCategory ?? string.Empty).Where(category => !string.IsNullOrEmpty(category)).ToListAsync();
            if (input.CheckOrganizationId != null)
            {
                var corg = await _context.Set<QHSESysOrganization>().FindAsync(input.CheckOrganizationId);
                var escapedcNamePath = EscapeLikePattern(corg.NamePath);
                newQuery =await query
                    .Join(_context.Set<QHSESysOrganization>(), // Join with QHSEOrganization table
                        joinResult => joinResult.CheckOrganizationId,
                        organization => organization.OrganizationId,
                        (joinResult, organization) => new { Danger = joinResult, Organization = organization })
                    .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedcNamePath}%", "/"))
                    .Select(joinResult => joinResult.Danger.DangerCategory ?? string.Empty).Where(category => !string.IsNullOrEmpty(category)).ToListAsync();
            }
            
            var dangerCategory = new List<string>();
            if (input.DangerCategory == "隐患类别")
            {
                dangerCategory = await _context.Set<QHSESysDangerCategory>().Where(u => u.ParentId == "0").Select(u => u.Item).ToListAsync();
            }
            else
            {
                dangerCategory = await _categoryService.GetSon(input.DangerCategory);
            }
            if (dangerCategory != null)
            {
                foreach (var category in dangerCategory)
                {
                    var children = await _categoryService.GetChildren(category);
                    var value =newQuery.Where(u => children.Contains(u)).Count();
                    result.Add(new DangerAnalysisResult { Name = category, Value = value });
                }
            }
        }
        result = result.OrderByDescending(result => result.Value).ToList();
        await _cacheService.SetTotalCategoryAnalysisAsync(input, result);
        return result;
    }
    public async Task<List<DangerAnalysisResult>> DangerTypeAnalysisAsync(DangerAnalysisInput input)
    {
        if (await _cacheService.GetTotalTypeAnalysisAsync(input) != null)
        {
            return await _cacheService.GetTotalTypeAnalysisAsync(input);
        }
        List<DangerAnalysisResult> result = new List<DangerAnalysisResult>();
        var query = _context.Set<QHSESysDanger>().AsNoTracking().AsQueryable();
        if (!string.IsNullOrEmpty(input.DangerOrganizationName) && input.TimeOn != null && input.TimeEnd != null && input.CheckType.Count != 0)
        {
            var org = await _context.Set<QHSESysOrganization>().FindAsync(input.DangerOrganizationId);
            var escapedNamePath = EscapeLikePattern(org.NamePath);
            query = query.Where(x => x.CheckDate >= input.TimeOn && x.CheckDate <= input.TimeEnd && input.CheckType.Contains(x.CheckType))
                 .Join(_context.Set<QHSESysOrganization>(), // Join with Organization table
                        danger => danger.OrganizationId,
                        organization => organization.OrganizationId,
                        (danger, organization) => new { Danger = danger, Organization = organization })
                    .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                    .Select(joinResult => joinResult.Danger);
            var newQuery = await query.Select(joinResult =>joinResult.DangerType).ToListAsync();
            if (input.CheckOrganizationId != null)
            {
                    var corg = await _context.Set<QHSESysOrganization>().FindAsync(input.CheckOrganizationId);
                    var escapedcNamePath = EscapeLikePattern(corg.NamePath);
                 newQuery =await query
                            .Join(_context.Set<QHSESysOrganization>(), // Join with QHSEOrganization table
                                joinResult => joinResult.CheckOrganizationId,
                                organization => organization.OrganizationId,
                                (joinResult, organization) => new { Danger = joinResult, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedcNamePath}%", "/"))
                            .Select(joinResult =>joinResult.Danger.DangerType).ToListAsync();
            }
            
            List<QHSEDictionaryModel> dangerType = await _dictionaryService.GetDictionaryAsync("1010");
            if (dangerType != null)
            {
                foreach (var type in dangerType)
                {
                    var value = newQuery.Where(u => type.Item.Equals(u)).Count();
                    DangerAnalysisResult res = new DangerAnalysisResult();
                    res.Name = type.Item;
                    res.Value = value;
                    result.Add(res);
                }
            }
        }
        result = result.OrderByDescending(result => result.Value).ToList();
        await _cacheService.SetTotalTypeAnalysisAsync(input, result);
        return result;
    }
    public async Task<List<DangerAnalysisResult>> DangerCauseAnalysisAsync(DangerAnalysisInput input)
    {
        if (await _cacheService.GetTotalCauseAnalysisAsync(input) != null)
        {
            return await _cacheService.GetTotalCauseAnalysisAsync(input);
        }
        List<DangerAnalysisResult> result = new List<DangerAnalysisResult>();
        var query = _context.Set<QHSESysDanger>().AsNoTracking().AsQueryable();
        if (!string.IsNullOrEmpty(input.DangerOrganizationName) && input.TimeOn != null && input.TimeEnd != null && input.CheckType.Count != 0)
        {
            var org = await _context.Set<QHSESysOrganization>().FindAsync(input.DangerOrganizationId);
            var escapedNamePath = EscapeLikePattern(org.NamePath);
            query = query.Where(x => x.CheckDate >= input.TimeOn && x.CheckDate <= input.TimeEnd && input.CheckType.Contains(x.CheckType))
                 .Join(_context.Set<QHSESysOrganization>(), // Join with Organization table
                        danger => danger.OrganizationId,
                        organization => organization.OrganizationId,
                        (danger, organization) => new { Danger = danger, Organization = organization })
                    .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                    .Select(joinResult => joinResult.Danger);
            var newQuery =await query.Select(joinResult =>joinResult.CauseAnalysis).ToListAsync();
            if (input.CheckOrganizationId != null)
            {
                  var corg = await _context.Set<QHSESysOrganization>().FindAsync(input.CheckOrganizationId);
                  var escapedcNamePath = EscapeLikePattern(corg.NamePath);
                  newQuery =await query
                        .Join(_context.Set<QHSESysOrganization>(), // Join with QHSEOrganization table
                            joinResult => joinResult.CheckOrganizationId,
                            organization => organization.OrganizationId,
                            (joinResult, organization) => new { Danger = joinResult, Organization = organization })
                        .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedcNamePath}%", "/"))
                        .Select(joinResult =>joinResult.Danger.CauseAnalysis).ToListAsync();
            }
            
            List<QHSEDictionaryModel> causeAnalysis = await _dictionaryService.GetDictionaryAsync("1016");
            if (causeAnalysis != null)
            {
                foreach (var cause in causeAnalysis)
                {
                    var value =newQuery.Where(u => cause.Item.Equals(u)).Count();
                    DangerAnalysisResult res = new DangerAnalysisResult();
                    res.Name = cause.Item;
                    res.Value = value;
                    result.Add(res);
                }
            }
        }
        result = result.OrderByDescending(result => result.Value).ToList();
        await _cacheService.SetTotalCauseAnalysisAsync(input, result);
        return result;
    }
    public async Task<List<DangerAnalysisResult>> DirectDepartmentAnalysisAsync(DangerAnalysisInput input)
    {
        if (await _cacheService.GetTotalDirectDepartmentAnalysisAsync(input) != null)
        {
            return await _cacheService.GetTotalDirectDepartmentAnalysisAsync(input);
        }
        List<DangerAnalysisResult> result = new List<DangerAnalysisResult>();
        var query = _context.Set<QHSESysDanger>().AsNoTracking().AsQueryable();
        if (!string.IsNullOrEmpty(input.DangerOrganizationName) && input.TimeOn != null && input.TimeEnd != null && input.CheckType.Count != 0)
        {
            var org = await _context.Set<QHSESysOrganization>().FindAsync(input.DangerOrganizationId);
            var escapedNamePath = EscapeLikePattern(org.NamePath);
            query = query.Where(x => x.CheckDate >= input.TimeOn && x.CheckDate <= input.TimeEnd && input.CheckType.Contains(x.CheckType))
                    .Join(_context.Set<QHSESysOrganization>(), // Join with Organization table
                        danger => danger.OrganizationId,
                        organization => organization.OrganizationId,
                        (danger, organization) => new { Danger = danger, Organization = organization })
                    .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                    .Select(joinResult => joinResult.Danger);
            var newQuery = await query.Select(joinResult =>joinResult.DirectDepartment).ToListAsync();
            if (input.CheckOrganizationId != null)
            {
                var corg = await _context.Set<QHSESysOrganization>().FindAsync(input.CheckOrganizationId);
                    var escapedcNamePath = EscapeLikePattern(corg.NamePath);
               newQuery =await query
                        .Join(_context.Set<QHSESysOrganization>(), // Join with QHSEOrganization table
                            joinResult => joinResult.CheckOrganizationId,
                            organization => organization.OrganizationId,
                            (joinResult, organization) => new { Danger = joinResult, Organization = organization })
                        .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedcNamePath}%", "/"))
                        .Select(joinResult =>joinResult.Danger.DirectDepartment).ToListAsync();
            }
               
            List<QHSEDictionaryModel> directDepartment = await _dictionaryService.GetDictionaryAsync("1008");
            if (directDepartment != null)
            {
                foreach (var department in directDepartment)
                {
                    var newitem = "";
                    if (department.Item.Contains("生产"))
                    {
                        newitem = "生产";
                    }
                    else if (department.Item.Contains("人力资源"))
                    {
                        newitem = "人力资源（培训）";
                    }
                    var value =newQuery.Where(u => department.Item.Contains(u) || newitem.Contains(u)).Count();
                    DangerAnalysisResult res = new DangerAnalysisResult();
                    res.Name = department.Item;
                    res.Value = value;
                    result.Add(res);
                }
            }
        }
        result = result.OrderByDescending(result => result.Value).ToList();
        await _cacheService.SetTotalDirectDepartmentAnalysisAsync(input, result);
        return result;
    }
    public async Task<List<DangerAnalysisResult>> HseElementAnalysisAsync(DangerAnalysisInput input)
    {
        if (await _cacheService.GetTotalHseAnalysisAsync(input) != null)
        {
            return await _cacheService.GetTotalHseAnalysisAsync(input);
        }
        List<DangerAnalysisResult> result = new List<DangerAnalysisResult>();
        var query = _context.Set<QHSESysDanger>().AsNoTracking().AsQueryable();
        if (!string.IsNullOrEmpty(input.DangerOrganizationName) && input.TimeOn != null && input.TimeEnd != null && input.CheckType.Count != 0)
        {
            var org = await _context.Set<QHSESysOrganization>().FindAsync(input.DangerOrganizationId);
            var escapedNamePath = EscapeLikePattern(org.NamePath);
            query = query.Where(x => x.CheckDate >= input.TimeOn && x.CheckDate <= input.TimeEnd && input.CheckType.Contains(x.CheckType))
                    .Join(_context.Set<QHSESysOrganization>(), // Join with Organization table
                        danger => danger.OrganizationId,
                        organization => organization.OrganizationId,
                        (danger, organization) => new { Danger = danger, Organization = organization })
                    .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                    .Select(joinResult => joinResult.Danger);
            var newQuery = await query.Select(joinResult => joinResult.HseElement).ToListAsync();
            if (input.CheckOrganizationId != null)
            {
                 var corg = await _context.Set<QHSESysOrganization>().FindAsync(input.CheckOrganizationId);
                 var escapedcNamePath = EscapeLikePattern(corg.NamePath);
                 newQuery =await query
                        .Join(_context.Set<QHSESysOrganization>(), // Join with QHSEOrganization table
                            joinResult => joinResult.CheckOrganizationId,
                            organization => organization.OrganizationId,
                            (joinResult, organization) => new { Danger = joinResult, Organization = organization })
                        .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedcNamePath}%", "/"))
                        .Select(joinResult => joinResult.Danger.HseElement).ToListAsync();
            }
            
            List<QHSEViolationHSEModel> hseElement = await _violationHSEService.GetAsync();
            if (hseElement != null)
            {
                foreach (var element in hseElement)
                {
                    var value =newQuery.Where(u =>element.Item.Equals(u)).Count();
                    DangerAnalysisResult res = new DangerAnalysisResult();
                    res.Name = element.Item;
                    res.Value = value;
                    result.Add(res);
                }
            }
        }
        result = result.OrderByDescending(result => result.Value).ToList();
        await _cacheService.SetTotalHseAnalysisAsync(input, result);
        return result;
    }
    public async Task<List<DangerAnalysisResult>> WorkSpecialtyAnalysisAsync(DangerAnalysisInput input)
    {
        if (await _cacheService.GetTotalSpecialtyAnalysisAsync(input) != null)
        {
            return await _cacheService.GetTotalSpecialtyAnalysisAsync(input);
        }
        List<DangerAnalysisResult> result = new List<DangerAnalysisResult>();
        var query = _context.Set<QHSESysDanger>().AsNoTracking().AsQueryable();
        if (!string.IsNullOrEmpty(input.DangerOrganizationName) && input.TimeOn != null && input.TimeEnd != null && input.CheckType.Count != 0)
        {
            var org = await _context.Set<QHSESysOrganization>().FindAsync(input.DangerOrganizationId);
            var escapedNamePath = EscapeLikePattern(org.NamePath);
            query = query.Where(x => x.CheckDate >= input.TimeOn && x.CheckDate <= input.TimeEnd && input.CheckType.Contains(x.CheckType))
                    .Join(_context.Set<QHSESysOrganization>(), // Join with Organization table
                        danger => danger.OrganizationId,
                        organization => organization.OrganizationId,
                        (danger, organization) => new { Danger = danger, Organization = organization })
                    .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                    .Select(joinResult => joinResult.Danger);
           var newQuery = await query.Where(u=> !string.IsNullOrEmpty(u.WorkSpecialty)).Select(joinResult => joinResult.WorkSpecialty).ToListAsync();
            if (input.CheckOrganizationId != null)
            {
                    var corg = await _context.Set<QHSESysOrganization>().FindAsync(input.CheckOrganizationId);
                    var escapedcNamePath = EscapeLikePattern(corg.NamePath);
                    newQuery =await query
                        .Join(_context.Set<QHSESysOrganization>(), // Join with QHSEOrganization table
                            joinResult => joinResult.CheckOrganizationId,
                            organization => organization.OrganizationId,
                            (joinResult, organization) => new { Danger = joinResult, Organization = organization })
                        .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedcNamePath}%", "/"))
                        .Select(joinResult =>joinResult.Danger.WorkSpecialty).ToListAsync();
            }
            
            List<QHSEKeyWorkSpecialtyModel> workSpecialty = await _keyWorkSpecialtyService.GetAsync();
            if (workSpecialty != null)
            {
                foreach (var specialty in workSpecialty)
                {
                    var value =newQuery.Where(u => specialty.Specialty.Contains(u)).Count();
                    DangerAnalysisResult res = new DangerAnalysisResult();
                    res.Name = specialty.Specialty;
                    res.Value = value;
                    result.Add(res);
                }
            }
        }
        await _cacheService.SetTotalSpecialtyAnalysisAsync(input, result);
        return result;
    }
    public async Task<List<DangerAnalysisResult>> DangerTypeCategoryAnalysisAsync(DangerAnalysisInput input)
    {
        if (await _cacheService.GetTotalTypeCategoryAnalysisAsync(input) != null)
        {
            return await _cacheService.GetTotalTypeCategoryAnalysisAsync(input);
        }
        List<DangerAnalysisResult> result = new List<DangerAnalysisResult>();
        var query = _context.Set<QHSESysDanger>().AsNoTracking().AsQueryable();
        if (!string.IsNullOrEmpty(input.DangerOrganizationName) && input.TimeOn != null && input.TimeEnd != null && input.CheckType.Count != 0)
        {
            var org = await _context.Set<QHSESysOrganization>().FindAsync(input.DangerOrganizationId);
            var escapedNamePath = EscapeLikePattern(org.NamePath);
            query = query.Where(x => x.CheckDate >= input.TimeOn && x.CheckDate <= input.TimeEnd && input.CheckType.Contains(x.CheckType))
                    .Join(_context.Set<QHSESysOrganization>(), // Join with Organization table
                        danger => danger.OrganizationId,
                        organization => organization.OrganizationId,
                        (danger, organization) => new { Danger = danger, Organization = organization })
                    .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                    .Select(joinResult => joinResult.Danger);
            var newQuery = await query.Where(category => !string.IsNullOrEmpty(category.DangerTypeCategory)).Select(joinResult => joinResult.DangerTypeCategory ?? string.Empty).ToListAsync();
            if (input.CheckOrganizationId != null)
            {
                var corg = await _context.Set<QHSESysOrganization>().FindAsync(input.CheckOrganizationId);
                var escapedcNamePath = EscapeLikePattern(corg.NamePath);
                newQuery = await query
                       .Join(_context.Set<QHSESysOrganization>(), // Join with QHSEOrganization table
                           joinResult => joinResult.CheckOrganizationId,
                           organization => organization.OrganizationId,
                           (joinResult, organization) => new { Danger = joinResult, Organization = organization })
                       .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedcNamePath}%", "/"))
                       .Where(category => !string.IsNullOrEmpty(category.Danger.DangerTypeCategory))
                       .Select(joinResult => joinResult.Danger.DangerTypeCategory ?? string.Empty).ToListAsync();
            }
            var dangerTypeCategorys = await _dictionaryService.GetDictionaryAsync("1013");
            if (dangerTypeCategorys != null)
            {
                foreach (var dangerTypeCategory in dangerTypeCategorys)
                {
                    if(dangerTypeCategory.Code == "10130003")
                    {
                        dangerTypeCategory.Item = "不区分+其他";
                    }
                    var value = newQuery.Where(u=>!string.IsNullOrEmpty(u)).Where(u =>dangerTypeCategory.Item.Contains(u)).Count();
                    if (dangerTypeCategory.Code == "10130003")
                    {
                        dangerTypeCategory.Item = "其他";
                    }
                    DangerAnalysisResult res = new DangerAnalysisResult();
                    res.Name = dangerTypeCategory.Item;
                    res.Value = value;
                    result.Add(res);
                }
            }
        }
        result = result.OrderByDescending(result => result.Value).ToList();
        await _cacheService.SetTotalTypeCategoryAnalysisAsync(input, result);
        return result;
    }
    public async Task<List<DangerAnalysisResult>> SecondaryUnitAnalysisAsync(DangerAnalysisInput input)
    {
        // 缓存查询
        var cacheResult = await _cacheService.GetTotalSecondaryUnitAnalysisAsync(input);
        if (cacheResult != null)
        {
            return cacheResult;
        }

        List<DangerAnalysisResult> result = new List<DangerAnalysisResult>();
        var query = _context.Set<QHSESysDanger>().AsNoTracking().AsQueryable();

        if (!string.IsNullOrEmpty(input.DangerOrganizationName) && input.TimeOn != null && input.TimeEnd != null && input.CheckType.Count != 0)
        {
            // 获取相关组织信息
            var dorg = await _context.Set<QHSESysOrganization>().FindAsync(input.DangerOrganizationId);
            if (dorg == null) return result;

            var escapedNamePath = EscapeLikePattern(dorg.NamePath);
            query = query.Where(x => x.CheckDate >= input.TimeOn && x.CheckDate <= input.TimeEnd && input.CheckType.Contains(x.CheckType));

            // 检查组织ID并查询
            if (!string.IsNullOrEmpty(input.CheckOrganizationId))
            {
                var corg = await _context.Set<QHSESysOrganization>().FindAsync(input.CheckOrganizationId);
                if (corg != null)
                {
                    var escapedcNamePath = EscapeLikePattern(corg.NamePath);
                    query = query.Join(_context.Set<QHSESysOrganization>(),
                            joinResult => joinResult.CheckOrganizationId,
                            organization => organization.OrganizationId,
                            (joinResult, organization) => new { Danger = joinResult, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedcNamePath}%", "/"))
                            .Select(joinResult => joinResult.Danger);
                }
            }

            // 联合查询和内存处理
            var joinedQuery = await query
                        .Join(_context.Set<QHSESysOrganization>(),
                              danger => danger.OrganizationId,
                              organization => organization.OrganizationId,
                              (danger, organization) => new { Danger = danger, Organization = organization })
                        .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                        .Select(u => u.Organization.NamePath)
                        .ToListAsync();

            List<QHSESysOrganization> orgs = await _organizationService.GetSon(input.DangerOrganizationId);
            if (orgs != null && joinedQuery.Any())
            {
                foreach (var org in orgs)
                {
                    var value = joinedQuery.Count(joinResult => joinResult.Contains(org.NamePath));
                    result.Add(new DangerAnalysisResult
                    {
                        Name = org.OrganizationName,
                        Value = value
                    });
                }
            }
        }

        // 排序并缓存结果
        result = result.OrderByDescending(result => result.Value).ToList();
        await _cacheService.SetTotalSecondaryUnitAnalysisAsync(input, result);

        return result;
    }

    public async Task<List<DangerAnalysisResult>> DangerStatusAnalysisAsync(DangerAnalysisInput input)
    {
        if (await _cacheService.GetTotalStatusAnalysisAsync(input) != null)
        {
            return await _cacheService.GetTotalStatusAnalysisAsync(input);
        }
        List<DangerAnalysisResult> result = new List<DangerAnalysisResult>();
        var query = _context.Set<QHSESysDanger>().AsNoTracking().AsQueryable();
        if (!string.IsNullOrEmpty(input.DangerOrganizationName) && input.TimeOn != null && input.TimeEnd != null && input.CheckType.Count != 0)
        {
            var org = await _context.Set<QHSESysOrganization>().FindAsync(input.DangerOrganizationId);
            var escapedNamePath = EscapeLikePattern(org.NamePath);
            query = query.Where(x => x.CheckDate >= input.TimeOn && x.CheckDate <= input.TimeEnd && input.CheckType.Contains(x.CheckType))
                    .Join(_context.Set<QHSESysOrganization>(), // Join with Organization table
                        danger => danger.OrganizationId,
                        organization => organization.OrganizationId,
                        (danger, organization) => new { Danger = danger, Organization = organization })
                    .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                    .Select(joinResult => joinResult.Danger);
            var newQuery = await query.Select(joinResult => joinResult.Status).ToListAsync();
            if (input.CheckOrganizationId != null)
            {
                var corg = await _context.Set<QHSESysOrganization>().FindAsync(input.CheckOrganizationId);
                var escapedcNamePath = EscapeLikePattern(corg.NamePath);
                newQuery = await query
                        .Join(_context.Set<QHSESysOrganization>(), // Join with QHSEOrganization table
                            joinResult => joinResult.CheckOrganizationId,
                            organization => organization.OrganizationId,
                            (joinResult, organization) => new { Danger = joinResult, Organization = organization })
                        .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedcNamePath}%", "/"))
                        .Select(joinResult => joinResult.Danger.Status)
                        .ToListAsync();
            }
                
            List<string> dangerStatus = new List<string>
            {
                "已整改",
                "未整改"
            };
            if (dangerStatus != null)
            {
                DangerAnalysisResult total = new DangerAnalysisResult();
                total.Name = "全部";
                total.Value = await query.CountAsync();
                result.Add(total);
                foreach (var status in dangerStatus)
                {
                    var value = newQuery.Where(u => status.Equals(u)).Count();
                    DangerAnalysisResult res = new DangerAnalysisResult();
                    res.Name = status;
                    res.Value = value;
                    result.Add(res);
                }
            }
        }
        result = result.OrderByDescending(result => result.Value).ToList();
        await _cacheService.SetTotalStatusAnalysisAsync(input, result);
        return result;
    }
    public async Task<List<DangerAnalysisResult>> DangerTrendAnalysisAsync(DangerTrendInput input)
    {
        if (await _cacheService.GetTotalTrendAnalysisAsync(input) != null)
        {
            return await _cacheService.GetTotalTrendAnalysisAsync(input);
        }
        List<DangerAnalysisResult> result = new List<DangerAnalysisResult>();
        var query = _context.Set<QHSESysDanger>().AsNoTracking().AsQueryable();

        if (!string.IsNullOrEmpty(input.DangerOrganizationName) && input.TimeOn != null && input.TimeEnd != null && input.CheckType.Count != 0 && !string.IsNullOrEmpty(input.TotalDate))
        {
            var org = await _context.Set<QHSESysOrganization>().FindAsync(input.DangerOrganizationId);
            var escapedNamePath = EscapeLikePattern(org.NamePath);
            query = query.Where(x => x.CheckDate >= input.TimeOn && x.CheckDate <= input.TimeEnd && input.CheckType.Contains(x.CheckType))
                    .Join(_context.Set<QHSESysOrganization>(), // Join with Organization table
                        danger => danger.OrganizationId,
                        organization => organization.OrganizationId,
                        (danger, organization) => new { Danger = danger, Organization = organization })
                    .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                    .Select(joinResult => joinResult.Danger);
               
            var newQuery = await query.Select(joinResult => joinResult.CheckDate).ToListAsync();
            if(input.CheckOrganizationId != null)
            {
                    var corg = await _context.Set<QHSESysOrganization>().FindAsync(input.CheckOrganizationId);
                    var escapedcNamePath = EscapeLikePattern(corg.NamePath);
                    newQuery =await query
                        .Join(_context.Set<QHSESysOrganization>(), // Join with QHSEOrganization table
                            joinResult => joinResult.CheckOrganizationId,
                            organization => organization.OrganizationId,
                            (joinResult, organization) => new { Danger = joinResult, Organization = organization })
                        .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedcNamePath}%", "/"))
                        .Select(joinResult => joinResult.Danger.CheckDate)
                        .ToListAsync();
            }
            
            switch (input.TotalDate)
            {
                case "周":
                    AddWeeklyDataAsync(input, newQuery, result);
                    break;
                case "月":
                    AddMonthlyDataAsync(input, newQuery, result);
                    break;
                case "季度":
                    AddQuarterlyDataAsync(input, newQuery, result);
                    break;
                default:
                    AddYearlyDataAsync(input, newQuery, result);
                    break;
            }
        }
        await _cacheService.SetTotalTrendAnalysisAsync(input, result);
        return result;
    }

    private void AddWeeklyDataAsync(DangerAnalysisInput input, List<DateTime> query, List<DangerAnalysisResult> result)
    {
        var yearOn = input.TimeOn?.Year ?? 0;
        var yearEnd = input.TimeEnd?.Year ?? 0;
        var weekOn = GetIso8601WeekOfYear(input.TimeOn.Value);
        var weekEnd = GetIso8601WeekOfYear(input.TimeEnd.Value);

        for (int year = yearOn; year <= yearEnd; year++)
        {
            int startWeek = (year == yearOn) ? weekOn : 1;
            int endWeek = (year == yearEnd) ? weekEnd : GetIso8601WeeksInYear(year);

            for (int week = startWeek; week <= endWeek; week++)
            {
                var startDate = FirstDateOfWeekISO8601(year, week);
                var endDate = startDate.AddDays(7);

                var value = query
                    .Where(u => u.Year == year && u >= startDate && u <= endDate)
                    .Count();

                DangerAnalysisResult res = new DangerAnalysisResult();
                res.Name = $"{year}年第{week}周";
                res.Value = value;
                result.Add(res);
            }
        }
    }

    private void AddMonthlyDataAsync(DangerAnalysisInput input, List<DateTime> query, List<DangerAnalysisResult> result)
    {
        var yearOn = input.TimeOn?.Year ?? 0;
        var yearEnd = input.TimeEnd?.Year ?? 0;
        var monthOn = input.TimeOn?.Month ?? 0;
        var monthEnd = input.TimeEnd?.Month ?? 0;

        for (int year = yearOn; year <= yearEnd; year++)
        {
            int startMonth = (year == yearOn) ? monthOn : 1;
            int endMonth = (year == yearEnd) ? monthEnd : 12;

            for (int month = startMonth; month <= endMonth; month++)
            {
                var startDate = new DateTime(year, month, 1);
                var endDate = startDate.AddMonths(1).AddDays(-1);

                var value = query
                    .Where(u => u.Year == year && u.Month == month)
                    .Count();

                DangerAnalysisResult res = new DangerAnalysisResult();
                res.Name = $"{year}年{month}月";
                res.Value = value;
                result.Add(res);
            }
        }
    }

    private void AddQuarterlyDataAsync(DangerAnalysisInput input, List<DateTime> query, List<DangerAnalysisResult> result)
    {
        var yearOn = input.TimeOn?.Year ?? 0;
        var yearEnd = input.TimeEnd?.Year ?? 0;
        var quarterOn = (input.TimeOn?.Month - 1) / 3 + 1;
        var quarterEnd = (input.TimeEnd?.Month - 1) / 3 + 1;

        for (int year = yearOn; year <= yearEnd; year++)
        {
            int startQuarter = (int)((year == yearOn) ? quarterOn : 1);
            int endQuarter = (int)((year == yearEnd) ? quarterEnd : 4);

            for (int quarter = startQuarter; quarter <= endQuarter; quarter++)
            {
                var startDate = new DateTime(year, (quarter - 1) * 3 + 1, 1);
                var endDate = startDate.AddMonths(3).AddDays(-1);

                var value = query
                    .Where(u => u.Year == year && u >= startDate && u <= endDate)
                    .Count();

                DangerAnalysisResult res = new DangerAnalysisResult();
                res.Name = $"{year}年第{quarter}季度";
                res.Value = value;
                result.Add(res);
            }
        }
    }

    private void AddYearlyDataAsync(DangerAnalysisInput input, List<DateTime> query, List<DangerAnalysisResult> result)
    {
        var yearOn = input.TimeOn?.Year ?? 0;
        var yearEnd = input.TimeEnd?.Year ?? 0;

        for (int year = yearOn; year <= yearEnd; year++)
        {
            var startDate = new DateTime(year, 1, 1);
            var endDate = new DateTime(year, 12, 31);

            var value = query
                .Where(u => u.Year == year)
                .Count();

            DangerAnalysisResult res = new DangerAnalysisResult();
            res.Name = $"{year}年";
            res.Value = value;
            result.Add(res);
        }
    }

    // Helper methods for ISO 8601 week calculation

    private int GetIso8601WeeksInYear(int year)
    {
        DateTime date = new DateTime(year, 12, 31);
        int weeksInYear = GetIso8601WeekOfYear(date);
        return weeksInYear;
    }

    private int GetIso8601WeekOfYear(DateTime date)
    {
        // Determine the week of the year for the given date
        int weekOfYear = CultureInfo.InvariantCulture.Calendar.GetWeekOfYear(date, CalendarWeekRule.FirstDay, DayOfWeek.Monday);

        // Check if the week is in the previous year
        if (weekOfYear == 52 && date.Month == 1)
        {
            weekOfYear = CultureInfo.InvariantCulture.Calendar.GetWeekOfYear(date.AddDays(-7), CalendarWeekRule.FirstDay, DayOfWeek.Monday);
        }

        return weekOfYear;
    }


    private DateTime FirstDateOfWeekISO8601(int year, int weekOfYear)
    {
        DateTime jan1 = new DateTime(year, 1, 1);
        int daysToFirstDay = (int)DayOfWeek.Monday - (int)jan1.DayOfWeek;

        if (daysToFirstDay > 0)
        {
            daysToFirstDay -= 7;
        }

        DateTime firstMonday = jan1.AddDays(daysToFirstDay);

        if (weekOfYear > 1)
        {
            firstMonday = firstMonday.AddDays((weekOfYear - 1) * 7);
        }

        return firstMonday;
    }
    public async Task<List<DangerAnalysisResult>> SpecialityDangerSpecialtyAnalysisAsync(DangerSpecialityInput input)
    {
        if (await _cacheService.GetSpecialityDangerSpecialtyAnalysisAsync(input) != null)
        {
            return await _cacheService.GetSpecialityDangerSpecialtyAnalysisAsync(input);
        }
        List<DangerAnalysisResult> result = new List<DangerAnalysisResult>();
        var query = _context.Set<QHSESysDanger>().AsNoTracking().AsQueryable();
        if (!string.IsNullOrEmpty(input.DangerOrganizationName) && !string.IsNullOrEmpty(input.WorkSpeciality)
            && input.TimeOn != null && input.TimeEnd != null && input.CheckType.Count != 0 && !string.IsNullOrEmpty(input.TotalDate))
        {
            var org = await _context.Set<QHSESysOrganization>().FindAsync(input.DangerOrganizationId);
            var escapedNamePath = EscapeLikePattern(org.NamePath);
            query = query.Where(u => input.CheckType.Contains(u.CheckType)&&  u.WorkSpecialty == input.WorkSpeciality && u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd)
                    .Join(_context.Set<QHSESysOrganization>(), // Join with Organization table
                        violation => violation.OrganizationId,
                        organization => organization.OrganizationId,
                        (violation, organization) => new { Violation = violation, Organization = organization })
                    .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                    .Select(joinResult => joinResult.Violation);
            var newQuery = await query.Select(joinResult => joinResult.CheckDate)
                        .ToListAsync();
            if (input.CheckOrganizationId != null)
            {
                var corg = await _context.Set<QHSESysOrganization>().FindAsync(input.CheckOrganizationId);
                    var escapedcNamePath = EscapeLikePattern(corg.NamePath);
                newQuery =await query
                        .Join(_context.Set<QHSESysOrganization>(), // Join with QHSEOrganization table
                            joinResult => joinResult.CheckOrganizationId,
                            organization => organization.OrganizationId,
                            (joinResult, organization) => new { Danger = joinResult, Organization = organization })
                        .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedcNamePath}%", "/"))
                        .Select(joinResult => joinResult.Danger.CheckDate)
                        .ToListAsync();
            }
            switch (input.TotalDate)
            {
                case "周":
                    AddWeeklyDataAsync(input, newQuery, result);
                    break;
                case "月":
                    AddMonthlyDataAsync(input, newQuery, result);
                    break;
                case "季度":
                    AddQuarterlyDataAsync(input, newQuery, result);
                    break;
                default:
                    AddYearlyDataAsync(input, newQuery, result);
                    break;
            }
        }
        await _cacheService.SetSpecialityDangerSpecialtyAnalysisAsync(input, result);
        return result;
    }

    public async Task<List<DangerAnalysisResult>> SpecialityDangerKeyWorkTypeAnalysisAsync(DangerSpecialityInput input)
    {
        if (await _cacheService.GetSpecialityKeyWorkAnalysisAsync(input) != null)
        {
            return await _cacheService.GetSpecialityKeyWorkAnalysisAsync(input);
        }
        List<DangerAnalysisResult> result = new List<DangerAnalysisResult>();
        var query = _context.Set<QHSESysDanger>().AsNoTracking().AsQueryable();
        if (!string.IsNullOrEmpty(input.DangerOrganizationName) && !string.IsNullOrEmpty(input.WorkSpeciality)
            && input.TimeOn != null && input.TimeEnd != null && input.CheckType.Count != 0)
        {
            var org = await _context.Set<QHSESysOrganization>().FindAsync(input.DangerOrganizationId);
            var escapedNamePath = EscapeLikePattern(org.NamePath);
            query = query.Where(u => input.CheckType.Contains(u.CheckType) && u.WorkSpecialty == input.WorkSpeciality && u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd)
                    .Join(_context.Set<QHSESysOrganization>(), // Join with Organization table
                        violation => violation.OrganizationId,
                        organization => organization.OrganizationId,
                        (violation, organization) => new { Violation = violation, Organization = organization })
                    .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                    .Select(joinResult => joinResult.Violation);
            var newQuery = query.Select(joinResult =>joinResult.WorkType);
            if (input.CheckOrganizationId != null)
            {
                var corg = await _context.Set<QHSESysOrganization>().FindAsync(input.CheckOrganizationId);
                    var escapedcNamePath = EscapeLikePattern(corg.NamePath);
                newQuery = query
                        .Join(_context.Set<QHSESysOrganization>(), // Join with QHSEOrganization table
                            joinResult => joinResult.CheckOrganizationId,
                            organization => organization.OrganizationId,
                            (joinResult, organization) => new { Danger = joinResult, Organization = organization })
                        .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedcNamePath}%", "/"))
                        .Select(joinResult =>joinResult.Danger.WorkType);
            }
            
            var keyWorkType = await _keyWorkTypeService.GetKeyWorktypesAsync(input.WorkSpeciality);
            if (keyWorkType != null)
            {
                var keyWorkCounts = await query.Where(u => keyWorkType.Contains(u.WorkType))
                .GroupBy(u => u.WorkType).Select(group => new
                {
                    WorkType = group.Key,
                    Count = group.Count()
                })
                .ToListAsync();
                // 构建结果列表
                result = keyWorkType.Select(p => new DangerAnalysisResult
                {
                    Name = p,
                    Value = keyWorkCounts.FirstOrDefault(pc => pc.WorkType == p)?.Count ?? 0
                }).ToList();
            }
        }
        result = result.OrderByDescending(result => result.Value).ToList();
        await _cacheService.SetSpecialityKeyWorkAnalysisAsync(input, result);
        return result;
    }

    public async Task<List<DangerAnalysisResult>> SpecialityDangerTypeAnalysisAsync(DangerSpecialityInput input)
    {
        if (await _cacheService.GetSpecialityDangerTypeAnalysisAsync(input) != null)
        {
            return await _cacheService.GetSpecialityDangerTypeAnalysisAsync(input);
        }
        List<DangerAnalysisResult> result = new List<DangerAnalysisResult>();
        var query = _context.Set<QHSESysDanger>().AsNoTracking().AsQueryable();
        if (!string.IsNullOrEmpty(input.DangerOrganizationName) && !string.IsNullOrEmpty(input.WorkSpeciality)
            && input.TimeOn != null && input.TimeEnd != null && input.CheckType.Count != 0)
        {
            var org = await _context.Set<QHSESysOrganization>().FindAsync(input.DangerOrganizationId);
            var escapedNamePath = EscapeLikePattern(org.NamePath);
            query = query.Where(u => input.CheckType.Contains(u.CheckType) && u.WorkSpecialty == input.WorkSpeciality && u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd)
                    .Join(_context.Set<QHSESysOrganization>(), // Join with Organization table
                        violation => violation.OrganizationId,
                        organization => organization.OrganizationId,
                        (violation, organization) => new { Violation = violation, Organization = organization })
                    .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                    .Select(joinResult => joinResult.Violation);
            var newQuery = query.Select(joinResult =>joinResult.DangerType);
            if (input.CheckOrganizationId != null)
            {
                    var corg = await _context.Set<QHSESysOrganization>().FindAsync(input.CheckOrganizationId);
                    var escapedcNamePath = EscapeLikePattern(corg.NamePath);
                    newQuery = query
                        .Join(_context.Set<QHSESysOrganization>(), // Join with QHSEOrganization table
                            joinResult => joinResult.CheckOrganizationId,
                            organization => organization.OrganizationId,
                            (joinResult, organization) => new { Danger = joinResult, Organization = organization })
                        .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedcNamePath}%", "/"))
                        .Select(joinResult =>joinResult.Danger.DangerType);
            }
            
            var dangerTypes = await _dictionaryService.GetDictionaryAsync("1010");
            if (dangerTypes != null)
            {
                foreach (var item in dangerTypes)
                {
                    var value = await newQuery.Where(u => item.Item == u).CountAsync();
                    DangerAnalysisResult res = new DangerAnalysisResult();
                    res.Name = item.Item;
                    res.Value = value;
                    result.Add(res);
                }
            }
        }
        result = result.OrderByDescending(result => result.Value).ToList();
        await _cacheService.SetSpecialityDangerTypeAnalysisAsync(input, result);
        return result;
    }
    public async Task<List<DangerAnalysisResult>> SpecialityDangerTypeCategoryAnalysisAsync(DangerSpecialityInput input)
    {
        if (await _cacheService.GetSpecialityDangerTypeCategoryAnalysisAsync(input) != null)
        {
            return await _cacheService.GetSpecialityDangerTypeCategoryAnalysisAsync(input);
        }
        List<DangerAnalysisResult> result = new List<DangerAnalysisResult>();
        var query = _context.Set<QHSESysDanger>().AsNoTracking().AsQueryable();
        if (!string.IsNullOrEmpty(input.DangerOrganizationName) && !string.IsNullOrEmpty(input.WorkSpeciality)
            && input.TimeOn != null && input.TimeEnd != null && input.CheckType.Count != 0)
        {
            var org = await _context.Set<QHSESysOrganization>().FindAsync(input.DangerOrganizationId);
            var escapedNamePath = EscapeLikePattern(org.NamePath);
            query = query.Where(u => input.CheckType.Contains(u.CheckType) && u.WorkSpecialty == input.WorkSpeciality && u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd)
                    .Join(_context.Set<QHSESysOrganization>(), // Join with Organization table
                        violation => violation.OrganizationId,
                        organization => organization.OrganizationId,
                        (violation, organization) => new { Violation = violation, Organization = organization })
                    .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                    .Select(joinResult => joinResult.Violation);
            var newQuery = query.Where(u=> !string.IsNullOrEmpty(u.DangerTypeCategory)).Select(joinResult => joinResult.DangerTypeCategory ?? string.Empty);
            if (input.CheckOrganizationId != null)
            {
                var corg = await _context.Set<QHSESysOrganization>().FindAsync(input.CheckOrganizationId);
                    var escapedcNamePath = EscapeLikePattern(corg.NamePath);
                newQuery = query
                        .Join(_context.Set<QHSESysOrganization>(), // Join with QHSEOrganization table
                            joinResult => joinResult.CheckOrganizationId,
                            organization => organization.OrganizationId,
                            (joinResult, organization) => new { Danger = joinResult, Organization = organization })
                        .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedcNamePath}%", "/"))
                        .Where(u => !string.IsNullOrEmpty(u.Danger.DangerTypeCategory))
                        .Select(joinResult => joinResult.Danger.DangerTypeCategory ?? string.Empty);
            }
            var dangerTypeCategorys = await _dictionaryService.GetDictionaryAsync("1013");
            if (dangerTypeCategorys != null)
            {
                foreach (var dangerTypeCategory in dangerTypeCategorys)
                {
                    if (dangerTypeCategory.Code == "10130003")
                    {
                        dangerTypeCategory.Item = "不区分+其他";
                    }
                    var value = await newQuery.Where(u => !string.IsNullOrEmpty(u)).Where(u => dangerTypeCategory.Item.Contains(u)).CountAsync();
                    if (dangerTypeCategory.Code == "10130003")
                    {
                        dangerTypeCategory.Item = "其他";
                    }
                    DangerAnalysisResult res = new DangerAnalysisResult();
                    res.Name = dangerTypeCategory.Item;
                    res.Value = value;
                    result.Add(res);
                }
            }
        }
        result = result.OrderByDescending(result => result.Value).ToList();
        await _cacheService.SetSpecialityDangerTypeCategoryAnalysisAsync(input, result);
        return result;
    }
    public async Task<List<DangerAnalysisResult>> SpecialityDangerLocationAnalysisAsync(DangerSpecialityInput input)
    {
        if (await _cacheService.GetSpecialityLocationAnalysisAsync(input) != null)
        {
            return await _cacheService.GetSpecialityLocationAnalysisAsync(input);
        }
        List<DangerAnalysisResult> result = new List<DangerAnalysisResult>();
        var query = _context.Set<QHSESysDanger>().AsNoTracking().AsQueryable();
        if (!string.IsNullOrEmpty(input.DangerOrganizationName) && !string.IsNullOrEmpty(input.WorkSpeciality)
            && input.TimeOn != null && input.TimeEnd != null && input.CheckType.Count != 0)
        {
            var org = await _context.Set<QHSESysOrganization>().FindAsync(input.DangerOrganizationId);
            var escapedNamePath = EscapeLikePattern(org.NamePath);
            query = query.Where(u => input.CheckType.Contains(u.CheckType) && u.WorkSpecialty == input.WorkSpeciality && u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd)
                    .Join(_context.Set<QHSESysOrganization>(), // Join with Organization table
                        violation => violation.OrganizationId,
                        organization => organization.OrganizationId,
                        (violation, organization) => new { Violation = violation, Organization = organization })
                    .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                    .Select(joinResult => joinResult.Violation);
            var newQuery = query.Select(joinResult => joinResult.Location);
            if (input.CheckOrganizationId != null)
            {
                    var corg = await _context.Set<QHSESysOrganization>().FindAsync(input.CheckOrganizationId);
                    var escapedcNamePath = EscapeLikePattern(corg.NamePath);
                    newQuery = query
                        .Join(_context.Set<QHSESysOrganization>(), // Join with QHSEOrganization table
                            joinResult => joinResult.CheckOrganizationId,
                            organization => organization.OrganizationId,
                            (joinResult, organization) => new { Danger = joinResult, Organization = organization })
                        .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedcNamePath}%", "/"))
                        .Select(joinResult => joinResult.Danger.Location);
            }
           
            var location = await _locaitionService.GetLocationAsync(input.WorkSpeciality);
            if (location != null)
            {
                foreach (var item in location)
                {
                    var value = await newQuery.Where(u => item.Location == u).CountAsync();
                    DangerAnalysisResult res = new DangerAnalysisResult();
                    res.Name = item.Location;
                    res.Value = value;
                    result.Add(res);
                }
            }
        }
        result = result.OrderByDescending(result => result.Value).ToList();
        await _cacheService.SetSpecialityLocationAnalysisAsync(input, result);
        return result;
    }
    public async Task<List<DangerAnalysisResult>> SpecialityDangerStatusAnalysisAsync(DangerSpecialityInput input)
    {
        if (await _cacheService.GetSpecialityStatusAnalysisAsync(input) != null)
        {
            return await _cacheService.GetSpecialityStatusAnalysisAsync(input);
        }
        List<DangerAnalysisResult> result = new List<DangerAnalysisResult>();
        var query = _context.Set<QHSESysDanger>().AsNoTracking().AsQueryable();
        if (!string.IsNullOrEmpty(input.DangerOrganizationName) && input.TimeOn != null && input.TimeEnd != null && input.CheckType.Count != 0)
        {
            var org = await _context.Set<QHSESysOrganization>().FindAsync(input.DangerOrganizationId);
            var escapedNamePath = EscapeLikePattern(org.NamePath);
            query = query.Where(u => input.CheckType.Contains(u.CheckType) && u.WorkSpecialty == input.WorkSpeciality && u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd)
                    .Join(_context.Set<QHSESysOrganization>(), // Join with Organization table
                        violation => violation.OrganizationId,
                        organization => organization.OrganizationId,
                        (violation, organization) => new { Violation = violation, Organization = organization })
                    .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                    .Select(joinResult => joinResult.Violation);
            var newQuery = query.Select(joinResult => joinResult.Status);
            if (input.CheckOrganizationId != null)
            {
                    var corg = await _context.Set<QHSESysOrganization>().FindAsync(input.CheckOrganizationId);
                    var escapedcNamePath = EscapeLikePattern(corg.NamePath);
                    newQuery = query
                        .Join(_context.Set<QHSESysOrganization>(), // Join with QHSEOrganization table
                            joinResult => joinResult.CheckOrganizationId,
                            organization => organization.OrganizationId,
                            (joinResult, organization) => new { Danger = joinResult, Organization = organization })
                        .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedcNamePath}%", "/"))
                        .Select(joinResult => joinResult.Danger.Status);
            }
            
            List<string> dangerStatus = new List<string>
            {
                "已整改",
                "未整改"
            };
            if (dangerStatus != null)
            {
                DangerAnalysisResult total = new DangerAnalysisResult();
                total.Name = "全部";
                total.Value = await query.CountAsync();
                result.Add(total);
                foreach (var status in dangerStatus)
                {
                    var value = await newQuery.Where(u => status.Contains(u)).CountAsync();
                    DangerAnalysisResult res = new DangerAnalysisResult();
                    res.Name = status;
                    res.Value = value;
                    result.Add(res);
                }
            }
        }
        result = result.OrderByDescending(result => result.Value).ToList();
        await _cacheService.SetSpecialityStatusAnalysisAsync(input, result);
        return result;
    }
    public async Task<List<DangerAnalysisResult>> SpecialityHseElementAnalysisAsync(DangerSpecialityInput input)
    {
        if (await _cacheService.GetSpecialityHseAnalysisAsync(input) != null)
        {
            return await _cacheService.GetSpecialityHseAnalysisAsync(input);
        }
        List<DangerAnalysisResult> result = new List<DangerAnalysisResult>();
        var query = _context.Set<QHSESysDanger>().AsNoTracking().AsQueryable();
        if (!string.IsNullOrEmpty(input.DangerOrganizationName) && input.TimeOn != null && input.TimeEnd != null && input.CheckType.Count != 0)
        {
            var org = await _context.Set<QHSESysOrganization>().FindAsync(input.DangerOrganizationId);
            var escapedNamePath = EscapeLikePattern(org.NamePath);
            query = query.Where(u => input.CheckType.Contains(u.CheckType) && u.WorkSpecialty == input.WorkSpeciality && u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd)
                    .Join(_context.Set<QHSESysOrganization>(), // Join with Organization table
                        violation => violation.OrganizationId,
                        organization => organization.OrganizationId,
                        (violation, organization) => new { Violation = violation, Organization = organization })
                    .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                    .Select(joinResult => joinResult.Violation);
            var newQuery = query.Select(joinResult => joinResult.HseElement);
            if (input.CheckOrganizationId != null)
            {
                var corg = await _context.Set<QHSESysOrganization>().FindAsync(input.CheckOrganizationId);
                    var escapedcNamePath = EscapeLikePattern(corg.NamePath);
                newQuery = query
                        .Join(_context.Set<QHSESysOrganization>(), // Join with QHSEOrganization table
                            joinResult => joinResult.CheckOrganizationId,
                            organization => organization.OrganizationId,
                            (joinResult, organization) => new { Danger = joinResult, Organization = organization })
                        .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedcNamePath}%", "/"))
                        .Select(joinResult => joinResult.Danger.HseElement);
            }
            
            List<QHSEViolationHSEModel> hseElement = await _violationHSEService.GetAsync();
            if (hseElement != null)
            {
                foreach (var element in hseElement)
                {
                    var value = await newQuery.Where(u => EF.Functions.Like(u, $"%{element.Item}%")).CountAsync();
                    DangerAnalysisResult res = new DangerAnalysisResult();
                    res.Name = element.Item;
                    res.Value = value;
                    result.Add(res);
                }
            }
        }
        result = result.OrderByDescending(result => result.Value).ToList();
        await _cacheService.SetSpecialityHseAnalysisAsync(input, result);
        return result;
    }
    public async Task<List<DangerAnalysisResult>> SpecialityDirectDepartmentAnalysisAsync(DangerSpecialityInput input)
    {
        if (await _cacheService.GetSpecialityDirectDepartmentAnalysisAsync(input) != null)
        {
            return await _cacheService.GetSpecialityDirectDepartmentAnalysisAsync(input);
        }
        List<DangerAnalysisResult> result = new List<DangerAnalysisResult>();
        var query = _context.Set<QHSESysDanger>().AsNoTracking().AsQueryable();
        if (!string.IsNullOrEmpty(input.DangerOrganizationName) && input.TimeOn != null && input.TimeEnd != null && input.CheckType.Count != 0)
        {
            var org = await _context.Set<QHSESysOrganization>().FindAsync(input.DangerOrganizationId);
            var escapedNamePath = EscapeLikePattern(org.NamePath);
            query = query.Where(u => input.CheckType.Contains(u.CheckType) && u.WorkSpecialty == input.WorkSpeciality && u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd)
                    .Join(_context.Set<QHSESysOrganization>(), // Join with Organization table
                        violation => violation.OrganizationId,
                        organization => organization.OrganizationId,
                        (violation, organization) => new { Violation = violation, Organization = organization })
                    .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                    .Select(joinResult => joinResult.Violation);
            var newQuery = query.Select(joinResult =>joinResult.DirectDepartment);
            if (input.CheckOrganizationId != null)
            {
                    var corg = await _context.Set<QHSESysOrganization>().FindAsync(input.CheckOrganizationId);
                    var escapedcNamePath = EscapeLikePattern(corg.NamePath);
                    newQuery = query
                        .Join(_context.Set<QHSESysOrganization>(), // Join with QHSEOrganization table
                            joinResult => joinResult.CheckOrganizationId,
                            organization => organization.OrganizationId,
                            (joinResult, organization) => new { Danger = joinResult, Organization = organization })
                        .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedcNamePath}%", "/"))
                        .Select(joinResult =>joinResult.Danger.DirectDepartment);
            }
            List<QHSEDictionaryModel> directDepartment = await _dictionaryService.GetDictionaryAsync("1008");
            if (directDepartment != null)
            {
                foreach (var department in directDepartment)
                {
                    var newitem = "";
                    if (department.Item.Contains("生产"))
                    {
                        newitem = "生产";
                    }
                    else if (department.Item.Contains("人力资源"))
                    {
                        newitem = "人力资源（培训）";
                    }
                    var value = await newQuery.Where(u => department.Item.Contains(u) || newitem.Contains(u)).CountAsync();
                    DangerAnalysisResult res = new DangerAnalysisResult();
                    res.Name = department.Item;
                    res.Value = value;
                    result.Add(res);
                }
            }
        }
        result = result.OrderByDescending(result => result.Value).ToList();
        await _cacheService.SetSpecialityDirectDepartmentAnalysisAsync(input, result);
        return result;
    }
    public async Task<List<DangerAnalysisResult>> SpecialityDangerCauseAnalysisAsync(DangerSpecialityInput input)
    {
        if (await _cacheService.GetSpecialityDangerCauseAnalysisAsync(input) != null)
        {
            return await _cacheService.GetSpecialityDangerCauseAnalysisAsync(input);
        }
        List<DangerAnalysisResult> result = new List<DangerAnalysisResult>();
        var query = _context.Set<QHSESysDanger>().AsNoTracking().AsQueryable();
        if (!string.IsNullOrEmpty(input.DangerOrganizationName) && input.TimeOn != null && input.TimeEnd != null && input.CheckType.Count != 0)
        {
            var org = await _context.Set<QHSESysOrganization>().FindAsync(input.DangerOrganizationId);
            var escapedNamePath = EscapeLikePattern(org.NamePath);
            query = query.Where(u => input.CheckType.Contains(u.CheckType) && u.WorkSpecialty == input.WorkSpeciality && u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd)
                    .Join(_context.Set<QHSESysOrganization>(), // Join with Organization table
                        violation => violation.OrganizationId,
                        organization => organization.OrganizationId,
                        (violation, organization) => new { Violation = violation, Organization = organization })
                    .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                    .Select(joinResult => joinResult.Violation);
            var newQuery = query.Select(joinResult =>joinResult.CauseAnalysis);
            if (input.CheckOrganizationId != null)
            {
                    var corg = await _context.Set<QHSESysOrganization>().FindAsync(input.CheckOrganizationId);
                    var escapedcNamePath = EscapeLikePattern(corg.NamePath);
                    newQuery = query
                        .Join(_context.Set<QHSESysUser>(), // Join with User table
                            danger => danger.UserId,
                            user => user.UserId,
                            (danger, user) => new { Danger = danger, User = user })
                        .Join(_context.Set<QHSESysOrganization>(), // Join with QHSEOrganization table
                            joinResult => joinResult.User.OrganizationId,
                            organization => organization.OrganizationId,
                            (joinResult, organization) => new { Danger = joinResult.Danger, User = joinResult.User, Organization = organization })
                        .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedcNamePath}%", "/"))
                        .Select(joinResult =>joinResult.Danger.CauseAnalysis);
            }
            
            List<QHSEDictionaryModel> causeAnalysis = await _dictionaryService.GetDictionaryAsync("1016");
            if (causeAnalysis != null)
            {
                foreach (var cause in causeAnalysis)
                {
                    var value = await newQuery.Where(u => cause.Item.Contains(u)).CountAsync();
                    DangerAnalysisResult res = new DangerAnalysisResult();
                    res.Name = cause.Item;
                    res.Value = value;
                    result.Add(res);
                }
            }
        }
        result = result.OrderByDescending(result => result.Value).ToList();
        await _cacheService.SetSpecialityDangerCauseAnalysisAsync(input, result);
        return result;
    }
    public async Task<List<DangerAnalysisResult>> SpecialityDangerCategoryAnalysisAsync(DangerCategoryInput input)
    {
        if (await _cacheService.GetSpecialityDangerCategoryAnalysisAsync(input) != null)
        {
            return await _cacheService.GetSpecialityDangerCategoryAnalysisAsync(input);
        }
        List<DangerAnalysisResult> result = new List<DangerAnalysisResult>();
        var query = _context.Set<QHSESysDanger>().AsNoTracking().AsQueryable();
        if (!string.IsNullOrEmpty(input.DangerCategory) && !string.IsNullOrEmpty(input.DangerOrganizationName) && input.TimeOn != null && input.TimeEnd != null && input.CheckType.Count != 0)
        {
            var org = await _context.Set<QHSESysOrganization>().FindAsync(input.DangerOrganizationId);
            var escapedNamePath = EscapeLikePattern(org.NamePath);
            query = query.Where(u => input.CheckType.Contains(u.CheckType) && u.WorkSpecialty == input.WorkSpeciality && u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd)
                    .Join(_context.Set<QHSESysOrganization>(), // Join with Organization table
                        violation => violation.OrganizationId,
                        organization => organization.OrganizationId,
                        (violation, organization) => new { Violation = violation, Organization = organization })
                    .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                    .Select(joinResult => joinResult.Violation);
            var newQuery = query.Where(category => !string.IsNullOrEmpty(category.DangerCategory)).Select(joinResult => joinResult.DangerCategory);
            if(input.CheckOrganizationId!= null)
            {
                var corg = await _context.Set<QHSESysOrganization>().FindAsync(input.CheckOrganizationId);
            var escapedcNamePath = EscapeLikePattern(corg.NamePath);
            newQuery = query
                .Join(_context.Set<QHSESysOrganization>(), // Join with QHSEOrganization table
                    joinResult => joinResult.CheckOrganizationId,
                    organization => organization.OrganizationId,
                    (joinResult, organization) => new { Danger = joinResult, Organization = organization })
                .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedcNamePath}%", "/"))
                .Where(category => !string.IsNullOrEmpty(category.Danger.DangerCategory))
                .Select(joinResult => joinResult.Danger.DangerCategory);
            }
            
            var dangerCategory = new List<string>();
            if (input.DangerCategory == "隐患类别")
            {
                dangerCategory = await _context.Set<QHSESysDangerCategory>().Where(u => u.ParentId == "0").Select(u => u.Item).ToListAsync();
            }
            else
            {
                dangerCategory = await _categoryService.GetSon(input.DangerCategory);
            }
            if (dangerCategory != null)
            {
                foreach (var category in dangerCategory)
                {
                    var children = await _categoryService.GetChildren(category);
                    var value = await newQuery.Where(u => children.Contains(u)).CountAsync();
                    DangerAnalysisResult res = new DangerAnalysisResult();
                    res.Name = category;
                    res.Value = value;
                    result.Add(res);
                }
            }
        }
        result = result.OrderByDescending(result => result.Value).ToList();
        await _cacheService.SetSpecialityDangerCategoryAnalysisAsync(input, result);
        return result;
    }

    public async Task<QHSEDangerAdministerResultModel> DangerAdministerAsync(QHSEDangerAdministerInputModel input)
    {
        QHSEDangerAdministerResultModel resultModel = new QHSEDangerAdministerResultModel();
        if(input.CheckTypes.Count == 0 && string.IsNullOrEmpty(input.DangerOrganizationName) && input.TimeOn == null && input.TimeEnd == null&& string.IsNullOrEmpty(input.WorkSpeciality) && input.DangerTypes.Count == 0)
        {
            return resultModel;
        }
        var query = _context.Set<QHSESysDanger>().AsNoTracking().AsQueryable();
        if (input.TimeOn != null && input.TimeEnd != null)
        {
            query = query.Where(u => u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd);
        }
        if (input.CheckTypes.Count != 0)
        {
            query = query.Where(u => input.CheckTypes.Contains(u.CheckType));
        }

        if (!string.IsNullOrEmpty(input.DangerOrganizationName))
        {
            var org = await _context.Set<QHSESysOrganization>().FindAsync(input.DangerOrganizationId);
            if (org != null)
            {
                var escapedNamePath = EscapeLikePattern(org.NamePath);
                query = query
                .Join(_context.Set<QHSESysOrganization>(), // Join with Organization table
                    danger => danger.OrganizationId,
                    organization => organization.OrganizationId,
                    (danger, organization) => new { Danger = danger, Organization = organization })
                .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                .Select(joinResult => joinResult.Danger);
            }
        }
        if (!string.IsNullOrEmpty(input.WorkSpeciality))
        {
            query = query.Where(u =>EF.Functions.Like(u.WorkSpecialty, $"%{input.WorkSpeciality}%"));
        }

        if (input.DangerTypes.Count != 0)
        {
            query = query.Where(u => input.DangerTypes.Contains(u.DangerType));
        }
        resultModel.DangerCount =await query.CountAsync();
        query = query.Where(u => u.Status == "未整改");
        
        var models = await query.Select(joinResult => new QHSEDangerModel
        {
            qhseXyDangerId = joinResult.QhseXyDangerId,
            CheckType = joinResult.CheckType,
            CheckName = joinResult.CheckName,
            CheckDate = joinResult.CheckDate,
            OrganizationName = joinResult.OrganizationName,
            CheckOrganizationId = joinResult.CheckOrganizationId,
            WorkItem = joinResult.WorkItem,
            Location = joinResult.Location,
            DangerType = joinResult.DangerType,
            Description = joinResult.Description,
            Status = joinResult.Status,
            DangerCategory = joinResult.DangerCategory,
            EnterSys = joinResult.EnterSys,
            WorkRisk = joinResult.WorkRisk,
            WorkType = joinResult.WorkType,
            WorkSpecialty = joinResult.WorkSpecialty,
            ErpNo = joinResult.ErpNo
        }).ToListAsync();
        
        var totalRows  = await query.CountAsync();
        resultModel.DangerRectification = totalRows;
        var result = new PageResultModel<QHSEDangerModel>
        {
            TotalRows = totalRows,
            Rows = models,
            PageNo = input.PageNo,
            PageSize = input.PageSize
        };
        result.SetPage(input);
        result.CountTotalPage();
        resultModel.result = result;
        resultModel.DangerRectified = resultModel.DangerCount - resultModel.DangerRectification;
        return resultModel;
    }
    public async Task<List<QHSEDangerAdministerRepeatResultModel>> DangerAdministerRepeatAsync(QHSEDangerAdministerRepeatInputModel input)
    {
        List<QHSEDangerAdministerRepeatResultModel> resultModel = new List<QHSEDangerAdministerRepeatResultModel>();
        if (input.CheckTypes.Count == 0 && string.IsNullOrEmpty(input.DangerOrganizationName) && input.TimeOn == null && input.TimeEnd == null && string.IsNullOrEmpty(input.WorkSpeciality) && input.DangerTypes.Count == 0 && input.Repeat ==0)
        {
            return resultModel;
        }
        var query = _context.Set<QHSESysDanger>().AsNoTracking().AsQueryable();
        if (input.TimeOn != null && input.TimeEnd != null)
        {
            query = query.Where(u => u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd);
        }
        if (input.CheckTypes.Count != 0)
        {
            query = query.Where(u => input.CheckTypes.Contains(u.CheckType));
        }

        if (!string.IsNullOrEmpty(input.DangerOrganizationName))
        {
            var org = await _context.Set<QHSESysOrganization>().FindAsync(input.DangerOrganizationId);
            if (org != null)
            {
                var escapedNamePath = EscapeLikePattern(org.NamePath);
                query = query
                .Join(_context.Set<QHSESysOrganization>(), // Join with Organization table
                    danger => danger.OrganizationId,
                    organization => organization.OrganizationId,
                    (danger, organization) => new { Danger = danger, Organization = organization })
                .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                .Select(joinResult => joinResult.Danger);
            }
        }
        

        if (!string.IsNullOrEmpty(input.WorkSpeciality))
        {
            query = query.Where(u => EF.Functions.Like(u.WorkSpecialty, $"%{input.WorkSpeciality}%"));
        }

        if (input.DangerTypes.Count != 0)
        {
            query = query.Where(u => input.DangerTypes.Contains(u.DangerType));
        }
        // 使用 GroupBy 和 Count 来统计每个 dangerCategory 的个数
        resultModel = await query
            .Select(u=>u.DangerCategory ?? string.Empty)
            .Where(category => !string.IsNullOrEmpty(category))
            .GroupBy(u => u)
            .Select(g => new QHSEDangerAdministerRepeatResultModel
            {
                DangerCategory = g.Key,
                Repeat = g.Count()
            })
            .Where(u=>u.Repeat > input.Repeat)
            .OrderBy(u => u.Repeat)
            .ToListAsync();
        return resultModel;
    }

    public async Task<PageResultModel<ViolationTotalResultModel>> DangerTotalQueryAsync(ViolationTotalInputModel input)
    {
        var resultModel = new PageResultModel<ViolationTotalResultModel>();
        if (input.TimeOn == null || input.TimeEnd == null)
        {
            return resultModel;
        }
        List<ViolationTotalResultModel> totalResultModel = new List<ViolationTotalResultModel>();
        // 尝试从缓存中获取结果
        var cachedResult = await _cacheService.GetDangerTotalQueryAsync(input);
        if (cachedResult != null)
        {
            return cachedResult;
        }

        // 获取所有二级单位
        var secondaryUnits = await _organizationService.GetSon("00");
        if (secondaryUnits == null || !secondaryUnits.Any())
        {
            return resultModel;
        }
        var dquery = _context.Set<QHSESysDanger>().AsNoTracking().Where(u => u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd).AsQueryable();
        if (!string.IsNullOrEmpty(input.InspectionMethod))
        {
            dquery = dquery.Where(u => input.InspectionMethod.Equals(u.InspectionMethod));
        }
        var totalModel = new ViolationTotalResultModel() {
                SecondaryUnit = "总计"
         };
        foreach (var unit in secondaryUnits)
        {

            var escapedNamePath = EscapeLikePattern(unit.NamePath);
            var dangerCounts = await (
                from violation in dquery
                join organization in _context.Set<QHSESysOrganization>() on violation.OrganizationId equals organization.OrganizationId
                where EF.Functions.Like(organization.NamePath, $"{escapedNamePath}%", "/")
                select violation.CheckType
            )
            .GroupBy(u => u)
            .Select(group => new
            {
                CheckType = group.Key,
                DangerCount = group.Count()
            })
            .ToDictionaryAsync(result => result.CheckType, result => result.DangerCount);


            var unitResult = CalculateDangerCounts(dangerCounts, unit.OrganizationName);
            AggregateTotals(totalModel, unitResult);
            totalResultModel.Add(unitResult);
        }

        totalResultModel.Add(totalModel);
        resultModel.TotalRows = secondaryUnits.Count();
        resultModel.PageNo = input.PageNo;
        resultModel.PageSize = input.PageSize;
        resultModel.Rows = totalResultModel;
        resultModel.TotalPage = resultModel.CountTotalPage();

        // 缓存结果
        await _cacheService.SetDangerTotalQueryAsync(input, resultModel);
        return resultModel;
    }

    private ViolationTotalResultModel CalculateDangerCounts(Dictionary<string, int> dangerCounts, string organizationName)
    {
        var result = new ViolationTotalResultModel
        {
            SecondaryUnit = organizationName
        };

        foreach (var kv in dangerCounts)
        {
            switch (kv.Key)
            {
                case "监督检查-驻井监督检查":
                    result.ResidentSupervisorDanger += kv.Value;
                    break;
                case "监督检查-巡回监督检查":
                    result.PatrolSupervisionDanger += kv.Value;
                    break;
                case "监督检查-旁站监督检查":
                    result.ElectronicSupervisionDanger += kv.Value;
                    break;
                case "基层队站-月检查":
                    result.MonthDanger += kv.Value;
                    break;
                case "基层队站-周检查":
                    result.WeekDanger += kv.Value;
                    break;
                case "岗位员工检查":
                    result.StaffDanger += kv.Value;
                    break;
                case "国家及地方检查":
                    result.NationalAndLocalDanger += kv.Value;
                    break;
                case "集团公司及中油技服检查":
                    result.GroupCompanyDanger += kv.Value;
                    break;
                case "公司检查-领导检查承包点督导":
                    result.CompanyLeadersDanger += kv.Value;
                    break;
                case "公司检查-体系审核":
                    result.CompanySystemDanger += kv.Value;
                    break;
                case "公司检查-专项检查":
                    result.CompanySpecificDanger += kv.Value;
                    break;
                case "二级单位-领导检查承包点督导":
                    result.SecondaryLeadersDanger += kv.Value;
                    break;
                case "二级单位-体系审核":
                    result.SecondarySystemDanger += kv.Value;
                    break;
                case "二级单位-专项检查":
                    result.SecondarySpecificDanger += kv.Value;
                    break;
                case "三级单位-领导检查":
                    result.ThirdLeadersDanger += kv.Value;
                    break;
                case "三级单位-专项检查":
                    result.ThirdSpecificDanger += kv.Value;
                    break;
            }
        }

        result.SupervisorDangerTotal = result.ResidentSupervisorDanger + result.PatrolSupervisionDanger + result.ElectronicSupervisionDanger;
        result.SelfInspectionDangerTotal = result.MonthDanger + result.WeekDanger + result.StaffDanger;
        result.SuperiorUnitDangerTotal = result.GroupCompanyDanger + result.NationalAndLocalDanger;
        result.CompanyDangerTotal = result.CompanyLeadersDanger + result.CompanySpecificDanger + result.CompanySystemDanger;
        result.SecondaryDangerTotal = result.SecondaryLeadersDanger + result.SecondarySpecificDanger + result.SecondarySystemDanger;
        result.ThirdDangerTotal = result.ThirdLeadersDanger + result.ThirdSpecificDanger;
        result.DangerTotal = result.SupervisorDangerTotal + result.SelfInspectionDangerTotal + result.SuperiorUnitDangerTotal + result.CompanyDangerTotal + result.SecondaryDangerTotal + result.ThirdDangerTotal;

        return result;
    }

    private void AggregateTotals(ViolationTotalResultModel totalModel, ViolationTotalResultModel unitResult)
    {
        totalModel.NationalAndLocalDanger += unitResult.NationalAndLocalDanger;
        totalModel.GroupCompanyDanger += unitResult.GroupCompanyDanger;
        totalModel.SuperiorUnitDangerTotal += unitResult.SuperiorUnitDangerTotal;
        totalModel.CompanyLeadersDanger += unitResult.CompanyLeadersDanger;
        totalModel.CompanySystemDanger += unitResult.CompanySystemDanger;
        totalModel.CompanySpecificDanger += unitResult.CompanySpecificDanger;
        totalModel.CompanyDangerTotal += unitResult.CompanyDangerTotal;
        totalModel.SecondaryLeadersDanger += unitResult.SecondaryLeadersDanger;
        totalModel.SecondarySystemDanger += unitResult.SecondarySystemDanger;
        totalModel.SecondarySpecificDanger += unitResult.SecondarySpecificDanger;
        totalModel.SecondaryDangerTotal += unitResult.SecondaryDangerTotal;
        totalModel.ThirdLeadersDanger += unitResult.ThirdLeadersDanger;
        totalModel.ThirdSpecificDanger += unitResult.ThirdSpecificDanger;
        totalModel.ThirdDangerTotal += unitResult.ThirdDangerTotal;
        totalModel.ResidentSupervisorDanger += unitResult.ResidentSupervisorDanger;
        totalModel.PatrolSupervisionDanger += unitResult.PatrolSupervisionDanger;
        totalModel.MonthDanger += unitResult.MonthDanger;
        totalModel.WeekDanger += unitResult.WeekDanger;
        totalModel.StaffDanger += unitResult.StaffDanger;
        totalModel.ElectronicSupervisionDanger += unitResult.ElectronicSupervisionDanger;
        totalModel.SelfInspectionDangerTotal += unitResult.SelfInspectionDangerTotal;
        totalModel.SupervisorDangerTotal += unitResult.SupervisorDangerTotal;
        totalModel.DangerTotal += unitResult.DangerTotal;
    }



    public async Task<PageResultModel<QHSEDangerModel>> ComGetErpPageAsync(ComDangerErpPageInputModel input)
    {
        var result = new PageResultModel<QHSEDangerModel>();
        if (!string.IsNullOrEmpty(input.ErpNo))
        {
             var query =  _context.Set<QHSESysDanger>().AsNoTracking().Where(u => u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd);
            query = query.Where(u => input.ErpNo.Equals(u.ErpNo));
            var totalRows = await query.CountAsync();
            var dangers = await query
                        .Join(_context.Set<QHSESysOrganization>(), // Join with QHSEOrganization table
                            danger => danger.OrganizationId,
                            organization => organization.OrganizationId,
                            (danger, organization) => new { Danger = danger, Organization = organization })
                    .Join(_context.Set<QHSESysOrganization>(),
                                dangerUserOrganization => dangerUserOrganization.Danger.CheckOrganizationId,
                                organization => organization.OrganizationId,
                                (dangerUserOrganization, organization) => new {
                                    Danger = dangerUserOrganization.Danger,
                                    OrganizationName = dangerUserOrganization.Organization.NamePath,
                                    CheckOrganizationName = organization.NamePath,
                                    CheckOrganizationId = organization.OrganizationId
                                })
                    .Select(joinResult => new QHSEDangerModel
                    {
                        qhseXyDangerId = joinResult.Danger.QhseXyDangerId,
                        CheckType = joinResult.Danger.CheckType,
                        CheckName = joinResult.Danger.CheckName,
                        CheckDate = joinResult.Danger.CheckDate,
                        OrganizationName = joinResult.OrganizationName,
                        CheckOrganizationName = joinResult.CheckOrganizationName,
                        CheckOrganizationId = joinResult.CheckOrganizationId,
                        WorkItem = joinResult.Danger.WorkItem,
                        Location = joinResult.Danger.Location,
                        DangerType = joinResult.Danger.DangerType,
                        Description = joinResult.Danger.Description,
                        Status = joinResult.Danger.Status,
                        ErpNo = joinResult.Danger.ErpNo
                    })
                        .OrderByDescending(u => u.CheckDate)
                        .Skip((input.PageNo - 1) * input.PageSize)
                        .Take(input.PageSize)
                        .ToListAsync();

            result = new PageResultModel<QHSEDangerModel>
            {
                TotalRows = totalRows,
                Rows = dangers,
                PageNo = input.PageNo,
                PageSize = input.PageSize
            };
            result.SetPage(input);
            result.CountTotalPage();
        }
        return result;
    }
    public async Task<PageResultModel<QHSEDangerModel>> ComGetErpPageExAsync(ComDangerErpPageInputModel input)
    {
        var result = new PageResultModel<QHSEDangerModel>();
        if (!string.IsNullOrEmpty(input.ErpNo))
        {
            var query = _context.Set<QHSESysDanger>().AsNoTracking().Where(u => u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd);
            query = query.Where(u => input.ErpNo.Equals(u.ErpNo));
            var totalRows = await query.CountAsync();
            var dangers = await query
                        .Join(_context.Set<QHSESysOrganization>(), // Join with QHSEOrganization table
                            danger => danger.OrganizationId,
                            organization => organization.OrganizationId,
                            (danger, organization) => new { Danger = danger, Organization = organization })
                    .Join(_context.Set<QHSESysOrganization>(),
                                dangerUserOrganization => dangerUserOrganization.Danger.CheckOrganizationId,
                                organization => organization.OrganizationId,
                                (dangerUserOrganization, organization) => new {
                                    Danger = dangerUserOrganization.Danger,
                                    OrganizationName = dangerUserOrganization.Organization.NamePath,
                                    CheckOrganizationName = organization.NamePath,
                                    CheckOrganizationId = organization.OrganizationId
                                })
                    .Select(joinResult => new QHSEDangerModel
                    {
                        qhseXyDangerId = joinResult.Danger.QhseXyDangerId,
                        CheckType = joinResult.Danger.CheckType,
                        CheckName = joinResult.Danger.CheckName,
                        CheckDate = joinResult.Danger.CheckDate,
                        OrganizationName = joinResult.OrganizationName,
                        CheckOrganizationName = joinResult.CheckOrganizationName,
                        WorkItem = joinResult.Danger.WorkItem,
                        WorkSpecialty = joinResult.Danger.WorkSpecialty,
                        WorkType = joinResult.Danger.WorkType,
                        WorkContent = joinResult.Danger.WorkContent,
                        WorkRisk = joinResult.Danger.WorkRisk,
                        Location = joinResult.Danger.Location,
                        DangerType = joinResult.Danger.DangerType,
                        Description = joinResult.Danger.Description,
                        DangerCategory = joinResult.Danger.DangerCategory,
                        HseElement = joinResult.Danger.HseElement,
                        CauseAnalysis = joinResult.Danger.CauseAnalysis,
                        DirectDepartment = joinResult.Danger.DirectDepartment,
                        PotentialConsequence = joinResult.Danger.PotentialConsequence,
                        ReformerUserName = joinResult.Danger.ReformerUserName,
                        ReformerDate = joinResult.Danger.ReformerDate,
                        Status = joinResult.Danger.Status,
                        AcceptanceDate = joinResult.Danger.AcceptanceDate,
                        CheckDescription = joinResult.Danger.CheckDescription,
                        ErpNo = joinResult.Danger.ErpNo,
                        DevName = joinResult.Danger.DevName,
                        SpecMod = joinResult.Danger.SpecMod
                    })
                        .OrderByDescending(u => u.CheckDate)
                        .Skip((input.PageNo - 1) * input.PageSize)
                        .Take(input.PageSize)
                        .ToListAsync();

            result = new PageResultModel<QHSEDangerModel>
            {
                TotalRows = totalRows,
                Rows = dangers,
                PageNo = input.PageNo,
                PageSize = input.PageSize
            };
            result.SetPage(input);
            result.CountTotalPage();
        }
        return result;
    }

    public async Task<byte[]> GenerateExcelErp(ComDangerErpPageInputModel model)
    {
        List<QHSEDangerModel> models = new List<QHSEDangerModel>();
        int totalPage = 0;

        // 获取总页数,一次查询1000条数据
        model.PageSize = 1000;
        PageResultModel<QHSEDangerModel> pageResultModel = await ComGetErpPageExAsync(model);
        totalPage = pageResultModel.TotalPage;

        for (int pageNo = 1; pageNo <= totalPage; pageNo++)
        {
            // 设置当前页码
            model.PageNo = pageNo;

            // 执行分页查询，获取当前页的数据
            pageResultModel = await ComGetErpPageExAsync(model);
            List<QHSEDangerModel> currentPageData = pageResultModel.Rows;

            // 将当前页的数据添加到总数据集合
            models.AddRange(currentPageData);
        }

        // 设置 ExcelPackage 的 LicenseContext
        ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
        using (var package = new ExcelPackage())
        {
            var worksheet = package.Workbook.Worksheets.Add("Sheet1");

            // 设置表头并设置字体加粗
            for (int col = 0; col <= 21; col++)
            {
                ExcelRange headerCell = worksheet.Cells[1, col + 1];
                headerCell.Value = GetHeaderTitleErp(col);
                headerCell.Style.Font.Bold = true;
            }

            // 填充数据
            for (int i = 0; i < models.Count; i++)
            {
                worksheet.Cells[i + 2, 1].Value = models[i].CheckOrganizationName;
                worksheet.Cells[i + 2, 2].Value = models[i].CheckName;
                worksheet.Cells[i + 2, 3].Value = models[i].CheckDate.ToString();
                worksheet.Cells[i + 2, 4].Value = models[i].CheckType;
                worksheet.Cells[i + 2, 5].Value = models[i].OrganizationName;
                worksheet.Cells[i + 2, 6].Value = models[i].WorkSpecialty;
                worksheet.Cells[i + 2, 7].Value = models[i].WorkItem;
                worksheet.Cells[i + 2, 8].Value = models[i].WorkType;
                worksheet.Cells[i + 2, 9].Value = models[i].WorkContent;
                worksheet.Cells[i + 2, 10].Value = models[i].WorkRisk;
                worksheet.Cells[i + 2, 11].Value = models[i].Location;
                worksheet.Cells[i + 2, 12].Value = models[i].DangerType;
                worksheet.Cells[i + 2, 13].Value = models[i].Description;
                worksheet.Cells[i + 2, 14].Value = models[i].DangerCategory;
                worksheet.Cells[i + 2, 15].Value = models[i].HseElement;
                worksheet.Cells[i + 2, 16].Value = models[i].CauseAnalysis;
                worksheet.Cells[i + 2, 17].Value = models[i].DirectDepartment;
                worksheet.Cells[i + 2, 18].Value = models[i].ReformerUserName;
                worksheet.Cells[i + 2, 19].Value = models[i].ReformerDate.ToString();
                worksheet.Cells[i + 2, 20].Value = models[i].Status;
                worksheet.Cells[i + 2, 21].Value = models[i].AcceptanceDate.ToString();
                worksheet.Cells[i + 2, 22].Value = models[i].CheckDescription;
                worksheet.Cells[i + 2, 23].Value = models[i].ErpNo;
                worksheet.Cells[i + 2, 24].Value = models[i].DevName;
                worksheet.Cells[i + 2, 25].Value = models[i].SpecMod;
                // 填充更多属性值...
            }

            // 自动调整列宽
            worksheet.Cells[worksheet.Dimension.Address].AutoFitColumns();

            // 将 Excel 数据保存到字节数组
            var bytes = package.GetAsByteArray();

            return bytes;
        }
    }

    public async Task<List<string>> ComGetErpListAsync(IdInputModel id)
    {
        var result = new List<string>();
        if(id == null)
        {
            return null;
        }
        var query = _context.Set<QHSESysDanger>()
                .AsNoTracking().AsQueryable();
        var org = await _context.Set<QHSESysOrganization>().FindAsync(id.Id);
        if (org != null && id.Id!="00")
        {
            var escapedNamePath = EscapeLikePattern(org.NamePath);
            query = query
            .Join(_context.Set<QHSESysOrganization>(), // Join with Organization table
                danger => danger.OrganizationId,
                organization => organization.OrganizationId,
                (danger, organization) => new { Danger = danger, Organization = organization })
            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedNamePath}%", "/"))
            .Select(joinResult => joinResult.Danger);
        }
        result = await query.Select(u => u.ErpNo).Where(u => u != null)
            .Distinct()
            .ToListAsync();
        return result;
    }
    private string GetHeaderTitleErp(int column)
    {
        switch (column)
        {
            case 0:
                return "检查单位";
            case 1:
                return "检查人员";
            case 2:
                return "检查时间";
            case 3:
                return "检查类型";
            case 4:
                return "隐患单位";
            case 5:
                return "作业专业";
            case 6:
                return "作业项目";
            case 7:
                return "关键作业类型";
            case 8:
                return "关键作业内容";
            case 9:
                return "作业风险等级";
            case 10:
                return "位置区域";
            case 11:
                return "隐患级别";
            case 12:
                return "隐患描述";
            case 13:
                return "隐患类别";
            case 14:
                return "对应要素体系";
            case 15:
                return "原因分析";
            case 16:
                return "归属部门";
            case 17:
                return "整改负责人";
            case 18:
                return "限期整改时间";
            case 19:
                return "隐患状态";
            case 20:
                return "验收时间";
            case 21:
                return "验收描述";
            case 22:
                return "设备ERP编号";
            case 23:
                return "设备名称";
            case 24:
                return "设备规格型号";
            default:
                return string.Empty;
        }
    }

    public async Task<bool> GetRepeatAsync(QHSEDangerRepeatModel repeatModel)
    {
        var id = await _context.Set<QHSESysDanger>().Where(u => u.CheckDate.Day == repeatModel.CheckDate.Day && u.OrganizationId == repeatModel.OrganizationId
         && u.Description == repeatModel.Description).Select(u => u.QhseXyDangerId).FirstOrDefaultAsync();
        if (id != null)
        {
            return true;
        }
        return false;
    }
}
